I am trying to get my database to show a zero when its NULL (I know in formulas you can use the IF NULL(“ColumnName”, ‘0’ but this does not work in a stand alone select statement without a formula). I was told I should try a CASE WHEN but I could not get it to work. I tried
CASE (“ColumnName”) IS NULL THEN ‘0’ ELSE “ColumnName”
And I got SQL error so I am obviously missing something - or can I not use CASE WHEN in LO Base?
I am running LO Base 4.0.0.3 on Ubuntu 13.4
Try
SELECT CASE WHEN "ColumnName" IS NULL THEN '0' ELSE "ColumnName" END AS "ColumnName" from MYTABLE;Thanks! That did work just fine, however I had to click Run SQL Command Directly for it to produce any results. Then I keep getting table not found errors when I try to combine the result sets into queries. Can anyone tell me does CASE WHEN statements only work directly on tables?
With LO 4.1.3.2 using the embedded HSQLDB engine I was able to inner join two queries into a third query without using the Run SQL Directly command. This did include a CASE WHEN clause. So I guess I need more detail on your problems.
When I tested the same queries on my personal laptop running LO 4.1.3.2 (as opposed to 4.0.0.3 on the office desktop) it worked without direct SQL. I was then able to successfully join my queries, and no driect SQL needed. So it looks like the office is doing an upgrade to LO 4.1.3.2. THANK YOU so much for your help!
Just set as not null in your sgl command when creating or changing column