asked on

Crystal Reports - Select Statement Help Needed

I need to account for "NULL" records when using the following select statement but don't know the correct syntax.

This statement works when all values are populated but I need to return the record regardless of the contents of the "picklist names" below.

Thanks in advance!

{CUST_CONTACT_POLICY.POLICYID} = {?Pm-CUST_POLICY.POLICYID} and
{TBL_ADDRESS_PICKLISTITEM.NAME} = "Business" and
{TBL_HOME_PHONE_PICKLISTITEM.NAME} = "Home" and
{TBL_WORK_PHONE_PICKLISTITEM.NAME} = "Business" and
{TBL_MOBILE_PHONE_PICKLISTITEM.NAME} = "Mobile" and
{TBL_OTHER_PICKLISTITEM.NAME} = "Other"

Open in new window

tigin44

just remove the condition

{TBL_OTHER_PICKLISTITEM.NAME} = "Other"

fron your select statement

jgbreeden

for any value that can be null, put an Isnull with an Or, using parens to make sure it evaulates correctly::

and
(isnull({TBL_OTHER_PICKLISTITEM.NAME}) or {TBL_OTHER_PICKLISTITEM.NAME} = "Other")

James0628

Forgive the obvious, but if you really want to include records 'regardless of the contents of the "picklist names" ', then just don't check those fields.

OTOH, if you want to include a record if a field has a specific value or is null, then, as was already mentioned, you need to use IsNull to check any field that might be null, and you need to do the IsNull test before you do any other test on that field.

James

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wellmet

ASKER

Here is the full SQL listing generated.

==================================

SELECT "TBL_CONTACT"."FULLNAME", "TBL_CONTACT"."CUST_Gender_042634687", "TBL_CONTACT"."CUST_BV_045559093", "TBL_CONTACT"."CUST_Smoker_043841906", "TBL_CONTACT"."BIRTHDATE", "CUST_CONTACT_POLICY"."POLICYID", "TBL_ADDRESS"."LINE1", "TBL_ADDRESS"."CITY", "TBL_ADDRESS"."STATE", "TBL_ADDRESS"."POSTALCODE", "TBL_HOME_PHONE"."NUMBERDISPLAY", "TBL_EMAIL"."ADDRESS", "TBL_WORK_PHONE"."NUMBERDISPLAY", "TBL_MOBILE_PHONE"."NUMBERDISPLAY", "TBL_OTHER_PHONE"."NUMBERDISPLAY", "TBL_CONTACT"."CUST_Insured_114525578", "TBL_ADDRESS_PICKLISTITEM"."NAME", "TBL_HOME_PHONE_PICKLISTITEM"."NAME", "TBL_WORK_PHONE_PICKLISTITEM"."NAME", "TBL_MOBILE_PHONE_PICKLISTITEM"."NAME", "TBL_OTHER_PICKLISTITEM"."NAME"
FROM ((((((((((("DIA_12202008"."dbo"."CUST_CONTACT_POLICY" "CUST_CONTACT_POLICY" LEFT OUTER JOIN "DIA_12202008"."dbo"."TBL_CONTACT" "TBL_CONTACT" ON "CUST_CONTACT_POLICY"."CONTACTID"="TBL_CONTACT"."CONTACTID") LEFT OUTER JOIN "DIA_12202008"."dbo"."TBL_ADDRESS" "TBL_ADDRESS" ON "TBL_CONTACT"."CONTACTID"="TBL_ADDRESS"."CONTACTID") INNER JOIN "DIA_12202008"."dbo"."TBL_EMAIL" "TBL_EMAIL" ON "TBL_CONTACT"."CONTACTID"="TBL_EMAIL"."CONTACTID") INNER JOIN "DIA_12202008"."dbo"."TBL_PHONE" "TBL_HOME_PHONE" ON "TBL_CONTACT"."CONTACTID"="TBL_HOME_PHONE"."CONTACTID") INNER JOIN "DIA_12202008"."dbo"."TBL_PHONE" "TBL_WORK_PHONE" ON "TBL_CONTACT"."CONTACTID"="TBL_WORK_PHONE"."CONTACTID") INNER JOIN "DIA_12202008"."dbo"."TBL_PHONE" "TBL_MOBILE_PHONE" ON "TBL_CONTACT"."CONTACTID"="TBL_MOBILE_PHONE"."CONTACTID") INNER JOIN "DIA_12202008"."dbo"."TBL_PHONE" "TBL_OTHER_PHONE" ON "TBL_CONTACT"."CONTACTID"="TBL_OTHER_PHONE"."CONTACTID") INNER JOIN "DIA_12202008"."dbo"."TBL_PICKLISTITEM" "TBL_HOME_PHONE_PICKLISTITEM" ON "TBL_HOME_PHONE"."TYPEID"="TBL_HOME_PHONE_PICKLISTITEM"."PICKLISTITEMID") INNER JOIN "DIA_12202008"."dbo"."TBL_PICKLISTITEM" "TBL_WORK_PHONE_PICKLISTITEM" ON "TBL_WORK_PHONE"."TYPEID"="TBL_WORK_PHONE_PICKLISTITEM"."PICKLISTITEMID") INNER JOIN "DIA_12202008"."dbo"."TBL_PICKLISTITEM" "TBL_MOBILE_PHONE_PICKLISTITEM" ON "TBL_MOBILE_PHONE"."TYPEID"="TBL_MOBILE_PHONE_PICKLISTITEM"."PICKLISTITEMID") INNER JOIN "DIA_12202008"."dbo"."TBL_PICKLISTITEM" "TBL_OTHER_PICKLISTITEM" ON "TBL_OTHER_PHONE"."TYPEID"="TBL_OTHER_PICKLISTITEM"."PICKLISTITEMID") INNER JOIN "DIA_12202008"."dbo"."TBL_PICKLISTITEM" "TBL_ADDRESS_PICKLISTITEM" ON "TBL_ADDRESS"."TYPEID"="TBL_ADDRESS_PICKLISTITEM"."PICKLISTITEMID"
WHERE "CUST_CONTACT_POLICY"."POLICYID"='1C9437E4-FD87-4817-B87D-09ECA7E792AE' AND "TBL_CONTACT"."CUST_Insured_114525578"=1 AND ("TBL_ADDRESS_PICKLISTITEM"."NAME" IS NULL OR "TBL_ADDRESS_PICKLISTITEM"."NAME"=N'Business') AND ("TBL_HOME_PHONE_PICKLISTITEM"."NAME" IS NULL OR "TBL_HOME_PHONE_PICKLISTITEM"."NAME"=N'Home') AND ("TBL_WORK_PHONE_PICKLISTITEM"."NAME" IS NULL OR "TBL_WORK_PHONE_PICKLISTITEM"."NAME"=N'Business') AND ("TBL_MOBILE_PHONE_PICKLISTITEM"."NAME" IS NULL OR "TBL_MOBILE_PHONE_PICKLISTITEM"."NAME"=N'Mobile') AND ("TBL_OTHER_PICKLISTITEM"."NAME" IS NULL OR "TBL_OTHER_PICKLISTITEM"."NAME"=N'Other')

tigin44

this sql statement returns the records that are having value of 'Other', 'Business', 'Home', along with the null's. if you want to get all the records mathing your criteria achange the part

AND ("TBL_ADDRESS_PICKLISTITEM"."NAME" IS NULL OR "TBL_ADDRESS_PICKLISTITEM"."NAME"=N'Business') AND ("TBL_HOME_PHONE_PICKLISTITEM"."NAME" IS NULL OR "TBL_HOME_PHONE_PICKLISTITEM"."NAME"=N'Home') AND ("TBL_WORK_PHONE_PICKLISTITEM"."NAME" IS NULL OR "TBL_WORK_PHONE_PICKLISTITEM"."NAME"=N'Business') AND ("TBL_MOBILE_PHONE_PICKLISTITEM"."NAME" IS NULL OR "TBL_MOBILE_PHONE_PICKLISTITEM"."NAME"=N'Mobile') AND ("TBL_OTHER_PICKLISTITEM"."NAME" IS NULL OR "TBL_OTHER_PICKLISTITEM"."NAME"=N'Other')

to

AND ("TBL_OTHER_PICKLISTITEM"."NAME" IS NULL OR "TBL_OTHER_PICKLISTITEM"."NAME" IN ('Other', 'Business', 'Home', 'Mobile' )

James0628

tigin44,

But he's not looking for all of those values in one column ("TBL_OTHER_PICKLISTITEM"."NAME"). He's checking different columns resulting from various joins with "TBL_PICKLISTITEM", each with its own alias ("TBL_ADDRESS_PICKLISTITEM", "TBL_HOME_PHONE_PICKLISTITEM", etc.). He's checking to see if "address" is "Business", and "home phone" is "Home", and "work phone" is "Business", etc.

wellmet,

mlmcc may be right. I see a lot of INNER JOINs there. If you're trying to include cases where, for example, no matching entry is found in the "TBL_HOME_PHONE_PICKLISTITEM" alias, then you need a LEFT OUTER JOIN.

If you're linking the tables in CR, you need to go to Database > "Database Expert" and change the links to LEFT OUTER.

James

wellmet

ASKER

I originally tried left outer joins and the results were no different. I think that the problem is the way that I am handling the nulls but I can't seem to find a way of structuring the request that works. James is correct. I am trying to find the various addresses and phone numbers from the picklist items. I probably should simplify the query so that it only looks for one item and get that working. I will post a query after I have done that and perhaps someone can tell me where my logic is failing.

wellmet

ASKER

After a lot more research and trial and error it appears that the only way to do what I am trying to do is with nested select statements and a custom command. I would like to thank everyone for their input.

SOLUTION

James0628

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wellmet

ASKER

I ended up creating SQL queries and used them as commands to get the information I needed. I'm not sure why Crystal didn't handle the queries properly but I managed to create a few that when used together produced the correct output. Thanks for all your comments.

wellmet

ASKER

I believe that the issue was that Crystal doesn't respect the left outer joins like James0628 pointed out that mimic had said so I split the points between the two of them. I think that it's possible that Crystal respects them when you issue them in a command.

James0628

You're welcome. Glad I could help.

James