Replace non-null values with blank or null values in SQL oracle SELECT query

Question

I'm basically trying to write a query where if an event occurs within the last 200 days, it will display column values for A, B, C, D, E, F But in this same query, if an event does not occur within the last 200 days (201 days & beyond), it will only display values in columns A and B.

My current query I tried to do two select statements using the UNION statement. I tried to do a CASE statements for column C, D, E and F for the SELECT after the UNION, but I could not get it to work.

This current query displays all values for all columns regardless of if it occurred < or > 200 days.

SELECT  te.columna,
    	c.columnb,
    	te.columnc,
    	v.columnd,
   	v.columne,
   	v.columnf
	FROM	table1 te, table2 c, table3 v
    	WHERE te.columng_id=c.columng_id
   	AND v.columne=te.columne
   	AND te.columnc > sysdate-200
 	UNION
    	SELECT te.columna,
    	c.columnb,
   	te.columnc,
   	v.columnd,
   	v.columne,
   	v.columnf
    	FROM	table1 te, table2 c, table3 v
   	WHERE te.columng_id=c.columng_id
   	AND v.columne=te.columne
   	AND te.columnc < sysdate-200

Oracle, sorry. I'm new to the whole SQL thing so I keep forgetting which one is which. I'm using SQL Plus to do this query right now. — Tootally
– Tootally, Commented Jan 2, 2015 at 5:51
I have to note that with using te.columnc > sysdate-200 and te.columnc < sysdate-200 you're missing a potential value. It probably won't affect the query at all but one of those operators should be "or equal to". That is, you can use "greater than" (>) together with "less than or equal to" (<=) or "less than" (<) with "greater than or equal to" (>=). You might also want to use the TRUNC() function to truncate the date. — David Faber
– David Faber, Commented Jan 2, 2015 at 14:42

Lalit Kumar B · Accepted Answer · 2015-01-02 05:54:03Z

2

Try CASE in Oracle -

 SELECT  te.columna,
          c.columnb,
          CASE WHEN te.columnc >= SYSDATE -200
           THEN te.columnc
           ELSE NULL
        END columnc,
        CASE WHEN te.columnc >= SYSDATE -200
           THEN te.columnd
           ELSE NULL
        END columnd,
        CASE WHEN te.columnc >= SYSDATE -200
           THEN v.columne
           ELSE NULL
        END columne,
        CASE WHEN te.columnc >= SYSDATE -200
           THEN v.columnf
           END columnf
    FROM    table1 te, table2 c, table3 v
        WHERE te.columng_id=c.columng_id
    AND v.columne=te.columne

answered Jan 2, 2015 at 5:54

Lalit Kumar B

49.5k13 gold badges104 silver badges131 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tootally Over a year ago

This works like wonder! Thank you so much, I guess I didn't realize I had to keep repeating the column name and the >= SYSDATE for every separate column.

Lalit Kumar B Over a year ago

Thanks for the feedback. And you could safely avoid the UNION statement. Please mark it as answered :-)

Collectives™ on Stack Overflow

Replace non-null values with blank or null values in SQL oracle SELECT query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related