Query returns null value for column, need to replace the null value with a value. I do not know how. Please help
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
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;
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;
精彩评论