Home » SQL & PL/SQL » SQL & PL/SQL » Case statement in From Clause
Case statement in From Clause [message #664253] Mon, 10 July 2017 06:42 Go to next message
ramya29p
Messages: 146
Registered: November 2007
Location: Chennai
Senior Member
Hi,
can we use case statement in From clause of select Query.

select case when 1 = 2 then 'DEPT' ELSE 'EMP' END AS TBL
FROM DUAL; 

Do we have any other option??
Re: Case statement in From Clause [message #664254 is a reply to message #664253] Mon, 10 July 2017 06:46 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Can you explain what are you trying to do?
Re: Case statement in From Clause [message #664255 is a reply to message #664254] Mon, 10 July 2017 07:01 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
This works in 12, but I'd strongly recommend not doing something like this because it's a solution to a problem you a) should not have and b) absolutely should not be "solving" in the database.

create table system.t1 (a number);
create table system.t2 (a number);



select * from (
select t1.*, 'a' flg from system.t1
union all
select t2.*, 'b' from system.t2
) where flg='a';
Re: Case statement in From Clause [message #664258 is a reply to message #664255] Mon, 10 July 2017 07:25 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
select case when 1 = 2 then 'DEPT' ELSE 'EMP' END AS TBL
FROM DUAL;

your example is NOT having a case in the from, you are having a case in the select portion and that is normal usage and has been available since version 9 and available in plsql since version 8
Re: Case statement in From Clause [message #664259 is a reply to message #664258] Mon, 10 July 2017 07:31 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Do we have any other option??
could learn how to write better SQL; since "DEPT" never occurs from posted statement.
Re: Case statement in From Clause [message #664261 is a reply to message #664258] Mon, 10 July 2017 10:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I think OP wants something like:
select * from case when 1 = 2 then DEPT ELSE EMP END AS TBL

I agree with John:
Quote:
Can you explain what are you trying to do?
And I'd add why? What is the business requirement that implies this?

Re: Case statement in From Clause [message #664262 is a reply to message #664261] Mon, 10 July 2017 10:08 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
That is exactly what execute immediate is for. But like Michel I can't think of why he wants to do this?
Re: Case statement in From Clause [message #664385 is a reply to message #664253] Fri, 14 July 2017 19:33 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_12.1.0.2.0> COLUMN tbl NEW_VALUE tname
SCOTT@orcl_12.1.0.2.0> SELECT CASE WHEN 1 = 2 THEN 'DEPT' ELSE 'EMP' END AS tbl FROM DUAL
  2  /

TBL
---
EMP

1 row selected.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM &tname
  2  /
old   1: SELECT * FROM &tname
new   1: SELECT * FROM EMP

     EMPNO ENAME      JOB              MGR HIREDATE               SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 Wed 17-Dec-1980        800                    20
      7499 ALLEN      SALESMAN        7698 Fri 20-Feb-1981       1600        300         30
      7521 WARD       SALESMAN        7698 Sun 22-Feb-1981       1250        500         30
      7566 JONES      MANAGER         7839 Thu 02-Apr-1981       2975                    20
      7654 MARTIN     SALESMAN        7698 Mon 28-Sep-1981       1250       1400         30
      7698 BLAKE      MANAGER         7839 Fri 01-May-1981       2850                    30
      7782 CLARK      MANAGER         7839 Tue 09-Jun-1981       2450                    10
      7788 SCOTT      ANALYST         7566 Thu 09-Dec-1982       3000                    20
      7839 KING       PRESIDENT            Tue 17-Nov-1981       5000                    10
      7844 TURNER     SALESMAN        7698 Tue 08-Sep-1981       1500          0         30
      7876 ADAMS      CLERK           7788 Wed 12-Jan-1983       1100                    20
      7900 JAMES      CLERK           7698 Thu 03-Dec-1981        950                    30
      7902 FORD       ANALYST         7566 Thu 03-Dec-1981       3000                    20
      7934 MILLER     CLERK           7782 Sat 23-Jan-1982       1300                    10

14 rows selected.
Previous Topic: Insert statement with bind variable and Create statement
Next Topic: Pivot columns and dynamically assign values based on column name
Goto Forum:
  


Current Time: Thu Mar 28 17:05:42 CDT 2024