As in a previous post, I am trying to create a mail merge form letter from a database. Not all addresses have a second line in the address, but some do. I do not want to have blank lines in the form letter so I figured I could write SQL to get the job done.
select
CASEWHEN Address2 is null THEN '' ELSE Address2 || char(10) || char(13) as Address
from
Publisher
I think if I had the proper way to test a column's value for null I'd be OK. Any idea? Yes, I've tried various flavors of COALESCE too and that did not work. Plus, that is not exactly what I need. If I could get the CASE to work I'd be all set.
Last edited by gibsons on Thu Sep 03, 2009 1:55 pm, edited 2 times in total.
if exp is true, v1 is returned, else v2) - use CASE WHEN instead
NOTE: When using CASEWHEN, it is ONE WORD ( no space ), and, it is also surrounded by parenthesis. Just as an FYI, the 'syntax' is the same as an IF statement in a spreadsheet, and like a spreadsheet, the CASEWHEN may be 'nested'. The arguments are separated by commas ( rather than semi-colons as in Calc ).
It may not suit your particular requirements, but FWIW there is an alternative approach applicable to creating a mail merge form letter. Set up conditional fields in the form letter to eliminate blank lines. See:
The conditional fields would be saved in the Writer document.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Just to come back on your original effort. Note:
1 you should place double quotes around field and table names.
2 you are missing the 'END' keyword in your 'CASE WHEN' statement.
3 you can not use 'CASE <field> WHEN NULL ...' because NULL is not an ordinary value of <field>, you should use 'CASE WHEN <field> IS NULL THEN ... ELSE ... END'
The following works in BASE-SQL and in 'Direct SQL mode': COALESCE( "Address2" || CHAR(10) || CHAR(13) || '---' , '') as "Address"
note that I added '---' to see the \n\r during testing. The nice thing here is that the result of concatenation with '||' and one value being an NULL value makes the complete result to a NULL value. I tried also this COALESCE( CONCAT(CONCAT(CONCAT("Address2", CHAR(10)), CHAR(13)), '---' ) , '') as "Address"
but that failed. Is this a bug?
When you want to use direct SQL mode, you can use also the following two variants:
CASEWHEN("Address2" IS NULL, '',"Address2" || CHAR(13) || CHAR(10) || '---') as "Address"
CASE WHEN "Address2" IS NULL THEN '' ELSE "Address2" || CHAR(13) || CHAR(10) || '---' END as "Address"
Success, Erik
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
Please try to use the COALESCE version.
The CASEWHEN does not work in BASE-SQL mode, but only in DIRECT-SQL mode with BASE's internal database HSQLDB. You enable DIRECT-SQL mode when you enable the button SQL with green check mark. (Search help for DIRECT SQL).
Erik
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
Thanks to all for your help. The last trick for me to get was that I had to enable direct SQL mode to get the CASE statement to work. Now all is fine.
I have to say, that button is very misleading. I had expected it to be some sort of auto check syntax or something. I would never have expected it to change how the SQL is processed. I guess I should read the manual.