Home » SQL & PL/SQL » SQL & PL/SQL » Display conditional column values (10.2.0.4.0)
Display conditional column values [message #582960] Tue, 23 April 2013 16:58 Go to next message
lott42
Messages: 100
Registered: June 2010
Senior Member
I'm using the Oracle Emp,Dept tables as my sample.

I want to display certain table column values based on some criteria. If met, display those values otherwise diplay other column values

For example:

So when dept.deptno=10, I want to display these 2 columns values
1. dept.deptno
2. dept.dname

otherwise, display these 2 columns values
1. dept.loc
2. null

Can this be done with one case, decode or "other" type of structure going thru the table one time??

SELECT emp.empno,
       emp.ename,
       CASE
         WHEN dept.deptno = 10 THEN
          to_char(dept.deptno)
         ELSE
          dept.loc
       END col1,
       CASE
         WHEN dept.deptno = 10 THEN
          dept.dname
         ELSE
          ''
       END col2
  FROM emp, dept
 WHERE emp.deptno = dept.deptno


   	EMPNO	ENAME	COL1	COL2
1	9999	MILLER	10	ACCOUNTING
2	7369	SMITH	DALLAS	
3	7499	ALLEN	CHICAGO	
4	7521	WARD	CHICAGO	
5	7566	JONES	DALLAS	
6	7654	MARTIN	CHICAGO	
7	7698	BLAKE	CHICAGO	
8	7782	CLARK	10	ACCOUNTING
9	7788	SCOTT	DALLAS	
10	7839	KING	10	ACCOUNTING
11	7844	TURNER	CHICAGO	
12	7876	ADAMS	DALLAS	
13	7900	JAMES	CHICAGO	
14	7902	FORD	DALLAS	
15	7934	MILLER	10	ACCOUNTING

Re: Display conditional column values [message #582961 is a reply to message #582960] Tue, 23 April 2013 17:18 Go to previous messageGo to next message
cookiemonster
Messages: 10971
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can't output two columns from one case statement, and really, why do you think you need to?
Re: Display conditional column values [message #582962 is a reply to message #582961] Tue, 23 April 2013 17:37 Go to previous messageGo to next message
lott42
Messages: 100
Registered: June 2010
Senior Member
I have actual data that contain "location" type data

if my "type_code" = "X" I want to display
  location.col1
  location.col2
  location.col3

else I want to display these columns
  location.col4
  location.col5


I could write two cursors with two different selects(Using emp,dept(to repreresnt "location"))

  
SELECT emp.empno, emp.ename, dept.deptno, dept.dname
  FROM emp, dept
 WHERE emp.deptno = dept.deptno
   AND dept.deptno = 10;


SELECT emp.empno, emp.ename, dept.loc
  FROM emp, dept
 WHERE emp.deptno = dept.deptno
   AND dept.deptno <> 10;


or one sql and check each value in cursor to see if it matches "type_code" = "X" and output accordingly or can I do this with one conditional "select" clause
Re: Display conditional column values [message #582963 is a reply to message #582962] Tue, 23 April 2013 18:39 Go to previous messageGo to next message
BlackSwan
Messages: 22837
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

please post actual DDL (CREATE TABLE) statements for all tables involved; since neither EMP nor DEPT tables contains TYPE_CODE column.
Re: Display conditional column values [message #582965 is a reply to message #582963] Tue, 23 April 2013 19:11 Go to previous messageGo to next message
lott42
Messages: 100
Registered: June 2010
Senior Member
BlackSwan wrote on Tue, 23 April 2013 16:39
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

please post actual DDL (CREATE TABLE) statements for all tables involved; since neither EMP nor DEPT tables contains TYPE_CODE column.


Is there a reason for not wanting to use the "dept" table to simulate the same query that I'm trying to write??

dept.deptno = 10


Columns to display
dept.deptno
dept.dname


dept.deptno <> 10


Columns to display
dept.loc

Re: Display conditional column values [message #582968 is a reply to message #582965] Wed, 24 April 2013 00:16 Go to previous messageGo to next message
Littlefoot
Messages: 19648
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
UNION might do that:
SQL> select to_char(deptno), dname
  2    from dept
  3    where deptno = 10
  4  union all
  5  select loc, null
  6    from dept
  7    where deptno <> 10;

TO_CHAR(DEPTNO)                          DNAME
---------------------------------------- --------------
10                                       ACCOUNTING
DALLAS
CHICAGO
BOSTON

SQL>

However, note that column datatypes must match, otherwise it just won't work:
SQL> select deptno, dname
  2    from dept
  3    where deptno = 10
  4  union all
  5  select loc, null
  6    from dept
  7    where deptno <> 10;
select deptno, dname
       *
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression


SQL>
Re: Display conditional column values [message #582998 is a reply to message #582968] Wed, 24 April 2013 09:38 Go to previous message
lott42
Messages: 100
Registered: June 2010
Senior Member
Littlefoot, thanks for taking the time to think about this question. This issue was more out of curiosity then anything else.

Previous Topic: best way to send an email in oracle
Next Topic: Compilation Error in Procedure
Goto Forum:
  


Current Time: Tue Sep 30 06:48:58 CDT 2014

Total time taken to generate the page: 0.08268 seconds