Ask Your Question

Base query name/address list exclude optional data [closed]

asked 2017-11-12 23:13:13 +0200

Joe Castor gravatar image

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
edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Joe Castor
close date 2018-03-14 20:46:42.868938

1 Answer

Sort by » oldest newest most voted

answered 2017-11-12 23:35:30 +0200

Ratslinger gravatar image

updated 2017-11-12 23:53:10 +0200

Hello @Joe Castor,

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 )


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

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


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.

edit flag offensive delete link more


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

Joe Castor gravatar imageJoe Castor ( 2017-11-13 00:58:27 +0200 )edit

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.

Ratslinger gravatar imageRatslinger ( 2017-11-13 01:13:06 +0200 )edit

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

Joe Castor gravatar imageJoe Castor ( 2017-11-13 16:34:17 +0200 )edit

Question Tools

1 follower


Asked: 2017-11-12 23:13:13 +0200

Seen: 39 times

Last updated: Nov 12 '17