Query returns null value for column, need to replace the null value with a value. I do not know how. Please help

Question

SELECT COL1, DATE_END
        FROM TABLE1 
        WHERE COL1 IN('1','2','3','4','5','6','7','8','9','10','11','12')
    ORDER BY 1;

is the query. and when executed it gives me

COL1   DATE_END
1                                  
2 
3
4
5
6
7
8
9
10
11
12

because in the table the records 1...12 have their date_end as null

and the query should be modified in a way that the output is

COL1  DATE_END    
1     12/31/9999
2     12/31/9999
3     12/31/9999
4     12/31/9999
5     12/31/9999
6     12/31/9999
7     12/31/9999
8     12/31/9999
9     12/31/9999
10    12/31/9999
11    12/31/9999
12    12/31/9999

I hope I am clear this time, as to what I was trying out

Please help me experts... Thanks in advance

I have used "----" as I did not know how to insert spaces in HTML

Joe Stefanelli · Accepted Answer · 2011-09-15 19:43:02Z

6

Use the COALESCE function to replace the NULL values. I'm also not a big fan of using ordinal positions in the ORDER BY clause, so I changed that as well.

SELECT COL1, COALESCE(DATE_END, '12/31/9999') AS DATE_END
    FROM TABLE1 
    WHERE COL1 IN('1','2','3','4','5','6','7','8','9','10','11','12') 
    ORDER BY COL1;

edited Sep 15, 2011 at 19:43

answered Sep 15, 2011 at 19:35

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

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

Comments

Wil · Accepted Answer · 2011-09-15 19:35:44Z

3

SELECT COL1, COALESCE(DATE_END, '12-31-9999') FROM TABLE1 WHERE COL1 IN('1','2','3','4','5','6','7','8','9','10','11','12') ORDER BY 1;

answered Sep 15, 2011 at 19:35

Wil

4,3101 gold badge19 silver badges15 bronze badges

Collectives™ on Stack Overflow

Query returns null value for column, need to replace the null value with a value. I do not know how. Please help

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related