Can I put it in a field like I do with simple calcuations? Sorry newbie to Libre.
CASE WHEN doesn’t work.
Please keep main question short and place further explanations in details.
Now you are missing some things. You don’t specify what database you are using - like HSQLDB or Firebird embedded or something else.
A query IS SQL (and in my opinion writing directly in SQL is the only way to go). You specify something doesn’t work but there is no mention of what you are actually trying to do. And the final result can vary depending upon the DB being used.
Please supply the additional detailed information - Do not use an answer. Either edit your question or use add a comment.
Edit: Just tested CASE statement in both HSQLDB & Firebird embedded from SQL & Design views and all work fine. Need your info.
Thanks Ratslinger, I wasn’t kidding about the newbie part. I know I’m running LibreOffice vs 6.2.8.2. No idea which DB comes with that. I’m decent with MS Access but haven’t done anything in SQL in…10 years(?) When I try to put the IF/THEN or WHEN CASE/THEN in a field in the query I get the error message about unknown column. I’m trying to write an IF/THEN statement for two fields in my query. If field2 - field 1>2 then field 1+2 else field 2… In other words the value for field2 can’t be more than field 1+2… Is that any better??? Thanks for any help you can give me.
It’s HSQLDB.
Hello,
Let me attempt to answer your question(s). LibreOffice comes with two embedded databases either of which can be used with Base - HSQLDB or Firebird. This is a dropdown selection when creating the Base file. After creation this can be seen at the botton of the main .odb screen:
HSQLDB:
Firebird:
Now the If/Then method available for both is CASE. The syntax for that is:
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END
So for your request (I believe I understood it correctly) would be:
CASE WHEN "field2" > "field1" + 2 THEN "field1" + 2 ELSE "field2" END
This does work in either embedded database. Here also is an HSQLDB Base file with a complete query:
Sample ------ CaseHSQLDB.odb
Please note there are many places with links to LO documentation:
Main page of this site (right column):
On LO download page (right column):
And even through internet search:
Got it to work. Thanks for the help.