We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

Can I use a CASE WHEN statement in LO Base? [closed]

asked 2013-11-28 18:08:54 +0200

Libre Lyrae gravatar image

updated 2020-07-27 00:15:44 +0200

Alex Kemp gravatar image

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 on Ubuntu 13.4

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-11-14 23:02:50.813968

1 Answer

Sort by » oldest newest most voted

answered 2013-11-28 19:40:11 +0200

w_whalley gravatar image


SELECT CASE WHEN "ColumnName" IS NULL THEN '0' ELSE "ColumnName" END AS "ColumnName" from MYTABLE;
edit flag offensive delete link more


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?

Libre Lyrae gravatar imageLibre Lyrae ( 2013-11-29 18:55:18 +0200 )edit

With LO 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.

w_whalley gravatar imagew_whalley ( 2013-11-29 19:47:31 +0200 )edit

When I tested the same queries on my personal laptop running LO (as opposed to 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 THANK YOU so much for your help!

Libre Lyrae gravatar imageLibre Lyrae ( 2013-11-29 22:31:53 +0200 )edit

Just set as not null in your sgl command when creating or changing column

dtison gravatar imagedtison ( 2015-05-31 14:00:46 +0200 )edit

Question Tools

1 follower


Asked: 2013-11-28 18:08:54 +0200

Seen: 4,501 times

Last updated: Nov 28 '13