Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

Base query name/address list exclude optional data

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