CODEWITHSUNDEEP
sqloracle-databaseif-statementcase
user2659035
user2659035

Reputation: 109

Oracle SQL Select IF/Then/Case into Column Results

So I have a case statement that I'm trying to figure out, and ultimately I don't think I have to do this as a case, but I tried doing it as a sub-query within the select statement and that did not work. 

SELECT ID.ID_VALUE AS ID,

(CASE WHEN OBJ.STATUSCD = 'TEST' THEN TO_CHAR(OBJ.DTTM,'MM/DD/YYYY') END) 
    AS TEST_DATE, 
(CASE WHEN OBJ.STATUSCD = 'NON-T' THEN TO_CHAR(OBJ.DTTM,'MM/DD/YYYY') END)
    AS NON-T_DATE

When I processes this, I end up with a result that contains 3 columns and 2 rows. The one row contains a TEST_DATE value in one column, and nothing in another. Then in the other row I have a value in NON-T_DATE and nothing in another cell. The ID values are the same so I don't know why I can't get the results in the same row without duplicates.

Here's an example of my results: 

     || ID  ||  TEST_DATE  ||  NON-T_DATE  ||
     || 10  ||  1/1/2015   ||     NULL     || 
     || 10  ||    NULL     ||   1/2/2015   ||

Upvotes: 0

Views: 165

Answers (4)

Justin Cave
Justin Cave

Reputation: 231661

It appears that you either want to pivot the data or use the old max(case approach

SELECT ID.ID_VALUE AS ID,
       MAX(CASE WHEN OBJ.STATUSCD = 'TEST' 
                THEN TO_CHAR(OBJ.DTTM,'MM/DD/YYYY')
                ELSE NULL
            END) AS TEST_DATE, 
       MAX(CASE WHEN OBJ.STATUSCD = 'NON-T' 
                THEN TO_CHAR(OBJ.DTTM,'MM/DD/YYYY') 
                ELSE NULL
            END) AS NON-T_DATE 
  FROM <<something>>
 GROUP BY id.id_value

The additional ELSE clauses aren't strictly needed. But I'd rather be explicit rather than hoping that the next person to read this code remembers that a CASE statement returns NULL if there are no matches and MAX will pick the non-NULL value from a set.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Use group by and aggregation functions:

SELECT ID.ID_VALUE AS ID,
       MAX(CASE WHEN OBJ.STATUSCD = 'TEST' THEN TO_CHAR(OBJ.DTTM,'MM/DD/YYYY') END) AS TEST_DATE, 
       MAX (CASE WHEN OBJ.STATUSCD = 'NON-T' THEN TO_CHAR(OBJ.DTTM,'MM/DD/YYYY') END) AS NON-T_DATE 
FROM . . .
GROUP BY ID.ID_VALUE;

Upvotes: 1

Sandeep
Sandeep

Reputation: 806

You should re-write your query as mentioned below

SELECT ID.ID_VALUE AS ID,
(CASE WHEN OBJ.STATUSCD = 'TEST' THEN TO_CHAR(OBJ.DTTM,'MM/DD/YYYY') 
WHEN OBJ.STATUSCD = 'NON-T' THEN TO_CHAR(OBJ.DTTM,'MM/DD/YYYY') END)
    AS NON-T_DATE 
FROM TABLE_NAME

Upvotes: 0

durbnpoisn
durbnpoisn

Reputation: 4669

Because it's a separate Case statement. The only way you could get them in one shot, is to run another pass, where that result set is a derived table, and print that out.

select ID_VALUE, TEST_DATE, NON-T_DATE   from
(SELECT ID.ID_VALUE AS ID,

(CASE WHEN OBJ.STATUSCD = 'TEST' THEN TO_CHAR(OBJ.DTTM,'MM/DD/YYYY') END) 
    AS TEST_DATE, 
(CASE WHEN OBJ.STATUSCD = 'NON-T' THEN TO_CHAR(OBJ.DTTM,'MM/DD/YYYY') END)
    AS NON-T_DATE ) derivedtable

Upvotes: 0

Related Questions