CODEWITHSUNDEEP
sqliteifnull
monty_bean
monty_bean

Reputation: 514

SQL IFNULL with condition and write CSV

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.

Upvotes: 1

Views: 57

Answers (2)

AndrewK
AndrewK

Reputation: 1303

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;

Upvotes: 1

AndrewK
AndrewK

Reputation: 1303

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;

Upvotes: 1

Related Questions