Base query name/address list exclude optional data

[JoeCastor]

I am trying to create a query to use for a name and address Report. My issue is that there are several fields that are optional and therefore NULL. I’m not having any luck with how to code these fields to be skipped if they are NULL and also not have the line feed occur if they are NULL. I’ve searched various resources and tried several ways without luck. I’ve removed them all from my current version for a fresh start. Any suggestions to make this work? The fields include Address2, and all the Alternate address fields. Below is my code. Thank you in advance for any help.

SELECT "MemberMaster"."FirstName" || ' ' || RTRIM( "MemberMaster"."LastName" ) || '   Bdg# ' || "MemberMaster"."MemberID" || CHAR( 13 ) || "MemberMaster"."Address" || CHAR( 13 ) || "MemberMaster"."Address2" || CHAR( 13 ) || "MemberMaster"."City" || ', ' || "MemberMaster"."StateOrProvince" || '  ' || "MemberMaster"."PostalCode" || CHAR( 13 ) || "MemberMaster"."SecondaryAddress" || CHAR( 13 ) || "MemberMaster"."AltAddress2" || CHAR( 13 ) || "MemberMaster"."SecondaryCity" || ' ,' || "MemberMaster"."SecondaryStateProvince" || '  ' || "MemberMaster"."SecondaryPostalCode" || CHAR( 13 ) || "MemberMaster"."EmailAddress" || CHAR( 13 ) || "View_Phones_Combined"."PhoneNumbers" || CHAR( 13 ) || CHAR( 13 ) "directory" FROM "MemberMaster", "View_Phones_Combined" WHERE "MemberMaster"."MemberID" = "View_Phones_Combined"."PhoneID" AND "MemberMaster"."NotActive" <> 1 AND "MemberMaster"."FirstName" != '|' ORDER BY "MemberMaster"."LastName" ASC, "MemberMaster"."MemberID" ASC, "MemberMaster"."FirstName" ASC

[Ratslinger]

Hello @JoeCastor,

You should be able to do this with CASEWHEN. For example, it appears your “Address2” is one of the optional fields. So, instead of:

|| "MemberMaster"."Address2" || CHAR( 13 )

Use:

|| CASEWHEN("Address2" IS NULL,'', "MemberMaster"."Address2" ||CHAR(13))

in its’ place. BTW, it is two apostrophes between the commas after IS NULL & before MemberMaster.

Edit:

Once you have this solved & go to create a report, I can almost sense another question regarding this field in a report. See my answer first in this post.

[JoeCastor]

Thank you so much. That did it. I’m going to brand CASEWHEN on the back of my hand so it’ll stick. You couldn’t guess where I was trying to go (sub SELECT for each one). Yes, you’re correct on the report. I experimented and it isn’t friendly. I had to stretch the box too big, space waster. I’ll check your Jaspersoft and see if it’s something I can pull off. I’m assuming it provides the HSQL code to put into a report shell in the database. Thanks again

[Ratslinger]

Jaspersoft itself isn’t very difficult although you need a connection much like SQL Workbenck/J. It’s way more functional than Report Builder (remember your multi-column report - 3 clicks to change to 2 or 3 or more columns as you wanted). Also have browser based report server available. CANNOT run reports directly from Base without additional code which is charged for. All reports generated in Jaspersoft by accessing DB tables via JDBC connector.

[JoeCastor]

Thank you again. I’ll go to work on Jaspersoft to see how I do.