• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 423
  • Last Modified:

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

0
wellmet
Asked:
wellmet
  • 5
  • 4
  • 2
  • +2
2 Solutions
 
tigin44Commented:
just remove the condition

{TBL_OTHER_PICKLISTITEM.NAME} = "Other"

fron your select statement
0
 
jgbreedenCommented:
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")
0
 
James0628Commented:
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
0
Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

 
mlmccCommented:
What is the full SQL?

Since you are joining tables, you may need to use LEFT OUTER JOINS and limit the selection criteria to the main table.

mlmcc
0
 
wellmetAuthor Commented:
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')
0
 
tigin44Commented:
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' )
0
 
James0628Commented:
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
0
 
wellmetAuthor Commented:
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.
0
 
wellmetAuthor Commented:
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.
0
 
James0628Commented:
First of all, I should say that I haven't tried to analyze the entire query that you posted, so there certainly could be nuances that I'm not aware of.  Having said that ...

 The WHERE that you posted looks OK, provided that the tables were joined with LEFT OUTER joins, but getting the LEFT OUTER joins in CR may be a problem.  mlmcc has said that if you reference a column in a LEFT OUTER joined table, CR changes the join to INNER.  Personally, I don't think I've seen that happen, and I saw one specific case where CR did _not_ seem to be doing that, but I also use stored procedures in most of my reports instead of reading tables directly.  If he says that CR will do that, I assume that it does (not every time, from what I've seen, but presumably at least some of the time).  So, when you tried LEFT OUTER joins, CR may have changed them.

 A query like the one you posted, but with LEFT OUTER joins, might work in a stored procedure or a CR "command" (similar to a stored procedure, but stored in CR).  Did you try that query, with LEFT OUTER joins, directly on the server (outside of CR)?

 James
0
 
wellmetAuthor Commented:
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.
0
 
wellmetAuthor Commented:
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.
0
 
James0628Commented:
You're welcome.  Glad I could help.

 James
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 5
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now