SQL IFNULL with condition and write CSV

Question

I have a table with null values and datatypes for all columns are integer/real. I'd like to write 'no data' if the field is null when writing the data to csv.

Here is the testTable.

id     test1  test2  test3
------ ------ ------ ------
1      1      2      3
2             5      6
3      7             9
4             11     12

I'd like to only display 'string' in column test1 where id is 2. My sql statement is

SELECT id, (ifnull(test1, 'string')) as test1, test2, test3 from testTable;

and it produces this.

id     test1  test2  test3
------ ------ ------ ------
1      1      2      3
2      string 5      6
3      7             9
4      string 11     12

Is there a way to put condition clause for ifnull or is there another way of producing this final table?

My desired final testTable.

id     test1  test2  test3
------ ------ ------ ------
1      1      2      3
2      string 5      6
3      7             9
4             11     12

Thank you.

score 1 · Answer 1 · answered Jul 22 '16 at 16:08

1

You could use a correlated subquery and only pull in 'string' when the id is 2:

SELECT id, ifnull(test1, 
                  (select 'string' from testTable tt 
                    where tt.id =2 and testTable.id = tt.id)) as test1, 
       test2, test3
 from testTable;

answered Jul 22 '16 at 16:08

AndrewK

1,223
2
16
25

I didn't know you could put a subquery there. Thank you! – monty_bean Jul 22 '16 at 16:11

score 1 · Accepted Answer · answered Jul 22 '16 at 16:18

1

It could also be done using a CASE statement as well, which might be cleaner and faster if there is large amounts of data.

SELECT id, 
       CASE WHEN test1 IS NULL AND id = 2 THEN 'string' ELSE test1 END,
       test2, 
       test3
  from testTable;

answered Jul 22 '16 at 16:18

AndrewK

1,223
2
16
25

Ah, I was using case when statement earlier and looking at your code, I got it completely wrong. Thanks again! – monty_bean Jul 22 '16 at 16:21

SQL IFNULL with condition and write CSV

2 Answers2