Case statement in From Clause [message #664253] |
Mon, 10 July 2017 06:42 |
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 #664255 is a reply to message #664254] |
Mon, 10 July 2017 07:01 |
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 |
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 #664385 is a reply to message #664253] |
Fri, 14 July 2017 19:33 |
|
Barbara Boehmer
Messages: 9097 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.
|
|
|