Ask Your Question
1

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

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

Libre Lyrae gravatar image

updated 2015-11-14 23:02:38 +0100

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 4.0.0.3 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
3

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

w_whalley gravatar image

Try

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

Comments

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 +0100 )edit

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.

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

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!

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

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

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

Question Tools

1 follower

Stats

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

Seen: 3,093 times

Last updated: Nov 28 '13