[Solved] Conditionally Include Column if not null

Creating tables and queries
Post Reply
gibsons
Posts: 9
Joined: Tue Sep 01, 2009 1:29 am
Location: Ottawa, Canada

[Solved] Conditionally Include Column if not null

Post by gibsons »

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.

This does not work. I get a cryptic SQL error:

Code: Select all

select 
	CASEWHEN Address2 THEN Address2 || char(10) || char(13) ELSE '' as Address
from
	Publisher
Neither does this:

Code: Select all

select 
	CASE Address2 WHEN null THEN '' ELSE Address2 || char(10) || char(13) as Address
from
	Publisher
Nor this:

Code: Select all

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.
MS-Vista 64-bit
Open Office 3.1
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Conditionally Include Column if not null

Post by Sliderule »

gibsons:

According to HSQL documentation, found at:

http://www.hsqldb.org/doc/guide/ch09.html#N1251E
HSQL Documentation: [url]http://www.hsqldb.org/doc/guide/ch09.html#N1251E[/url] wrote:
CASEWHEN(exp,v1,v2)

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 ). :bravo:

Code: Select all

SELECT 
   CASEWHEN( LENGTH( COALESCE( "Address2", '' ) ) = 0, "Address1", "Address1" || CHAR( 10 ) || CHAR( 13 ) || "Address2" ) AS "Print Address"
From "Publisher"
I hope this helps, please be sure to let me / us know. :super:

Sliderule

Thanks to add [Solved] in your first post title (edit button) if your issue has been fixed / resolved.
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Conditionally Include Column if not null

Post by Arineckaig »

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:

http://openoffice.blogs.com/openoffice/ ... ument.html

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
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Conditionally Include Column if not null

Post by eremmel »

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)
gibsons
Posts: 9
Joined: Tue Sep 01, 2009 1:29 am
Location: Ottawa, Canada

Re: Conditionally Include Column if not null

Post by gibsons »

Thanks Erik. However, I can't get either one to work. :(

When I enter this:

Code: Select all

SELECT CASEWHEN("Address2" IS NULL, '', "Address2") AS "Address" FROM "Publisher"
I get the following [cryptic] error:

Code: Select all

SQL Status: HY000
Error code: 1000

syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE
Any ideas?

Stephen.
MS-Vista 64-bit
Open Office 3.1
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Conditionally Include Column if not null

Post by eremmel »

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)
gibsons
Posts: 9
Joined: Tue Sep 01, 2009 1:29 am
Location: Ottawa, Canada

Re: [Solved] Conditionally Include Column if not null

Post by gibsons »

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. ;)
MS-Vista 64-bit
Open Office 3.1
Post Reply