Isnull in selection expert 1

[mchambers]

Hello,

I am trying to pull data that is null using the selection expert.
I have a report that uses a table that has description fields that I joined with my patient table so I can show the description on the report. The problem I am having is if the actual field in the patient table is missing the patient's info does not show.

Here is what I have in the selection expert:

{Global_Code_Sex.category} = "SEX" and
{Global_Code_Occu.category} = "OS" and
{Global_Code_Stud.category} = "SS" and
{Global_Code_Educ.category} = "ED" and
{Global_Code_LA.category} = "LA" and
{Global_Code_RS.category} = "RS" and
{Global_Code_Legal.category} = "CRTDISP" and
{Global_Code_primIn.category} = "PINCOSRC" and
{Global_Code_Race.category} = "R" and
{Global_Code_MStatus.category} = "MS" and
{Global_Code_Sal.category} = "SA"

Here is my sql query maybe my joins are off:

 SELECT "Patient"."patient_id", "Patient"."fname", "Patient"."lname", "Patient"."addr_1", "Patient"."episode_id", "Patient"."dob", "Patient"."addr_2", "Patient"."city", "Patient"."state", "Patient"."zip", "Patient"."county", "Patient"."home_phone", "Patient"."work_phone", "Patient"."other_phone", "Patient"."occ_1", "Patient"."employer_name", "Patient_Custom"."incopers", "Intake_Med_Hx"."hearing_impaired", "Intake_Med_Hx"."movement_impaired", "Intake_Med_Hx"."sight_impaired", "Intake_Med_Hx"."speech_impaired", "Intake_Med_Hx"."learning_impaired", "Intake_Med_Hx"."pregnant", "Patient"."no_of_dependents", "Patient"."num_in_household", "Patient"."household_income", "Global_Code_MStatus"."category", "Global_Code_Race"."category", "Global_Code_Sex"."category", "Global_Code_Occu"."category", "Global_Code_Stud"."category", "Global_Code_Educ"."category", "Global_Code_LA"."category", "Global_Code_RS"."category", "Global_Code_primIn"."category", "Global_Code_Legal"."category", "Psychiatric_History"."event_name", "Global_Code_Sal"."category", "Global_Code_Legal"."name", "Global_Code_primIn"."name", "Global_Code_RS"."name", "Global_Code_LA"."name", "Global_Code_Stud"."name", "Global_Code_Educ"."name", "Global_Code_Milt"."name", "Global_Code_Occu"."name", "Global_Code_MStatus"."name", "Global_Code_Race"."name", "Global_Code_Sal"."name", "Global_Code_Sex"."name", "Global_Code_FS"."name", "Custom_Dropdowns_Eth"."field_description", "Custom_Dropdowns_Phy"."field_description", "Chemical_Dependency"."drug"
 FROM   (((((((((((((((((((("Patient" "Patient" LEFT OUTER JOIN "Intake_Med_Hx" "Intake_Med_Hx" ON ("Patient"."patient_id"="Intake_Med_Hx"."patient_id") AND ("Patient"."episode_id"="Intake_Med_Hx"."episode_id")) LEFT OUTER JOIN "Patient_Legal" "Patient_Legal" ON ("Patient"."patient_id"="Patient_Legal"."patient_id") AND ("Patient"."episode_id"="Patient_Legal"."episode_id")) INNER JOIN "Patient_Risk_Assessment" "Patient_Risk_Assessment" ON ("Patient"."patient_id"="Patient_Risk_Assessment"."patient_id") AND ("Patient"."episode_id"="Patient_Risk_Assessment"."episode_id")) LEFT OUTER JOIN "Patient_Custom" "Patient_Custom" ON ("Patient"."patient_id"="Patient_Custom"."patient_id") AND ("Patient"."episode_id"="Patient_Custom"."episode_id")) LEFT OUTER JOIN "Global_Code" "Global_Code_Sex" ON "Patient"."sex"="Global_Code_Sex"."code") LEFT OUTER JOIN "Global_Code" "Global_Code_Stud" ON "Patient"."student_status"="Global_Code_Stud"."code") LEFT OUTER JOIN "Global_Code" "Global_Code_Race" ON "Patient"."race"="Global_Code_Race"."code") LEFT OUTER JOIN "Global_Code" "Global_Code_MStatus" ON "Patient"."marital_status"="Global_Code_MStatus"."code") LEFT OUTER JOIN "Global_Code" "Global_Code_Sal" ON "Patient"."salary"="Global_Code_Sal"."code") LEFT OUTER JOIN "Global_Code" "Global_Code_Occu" ON "Patient"."occ_status"="Global_Code_Occu"."code") LEFT OUTER JOIN "Global_Code" "Global_Code_Educ" ON "Patient"."ed_level"="Global_Code_Educ"."code") LEFT OUTER JOIN "Global_Code" "Global_Code_Milt" ON "Patient"."military_status"="Global_Code_Milt"."code") LEFT OUTER JOIN "Global_Code" "Global_Code_RS" ON "Patient"."referral_source"="Global_Code_RS"."code") LEFT OUTER JOIN "Global_Code" "Global_Code_LA" ON "Patient"."living_arrangement_1"="Global_Code_LA"."code") LEFT OUTER JOIN "Psychiatric_History" "Psychiatric_History" ON ("Patient"."patient_id"="Psychiatric_History"."patient_id") AND ("Patient"."episode_id"="Psychiatric_History"."episode_id")) LEFT OUTER JOIN "Global_Code" "Global_Code_FS" ON "Patient"."fin_status"="Global_Code_FS"."code") LEFT OUTER JOIN "Global_Code" "Global_Code_Legal" ON "Patient_Legal"."crt_disp"="Global_Code_Legal"."code") LEFT OUTER JOIN "Chemical_Dependency" "Chemical_Dependency" ON "Patient_Risk_Assessment"."session_no"="Chemical_Dependency"."session_no") LEFT OUTER JOIN "Global_Code" "Global_Code_primIn" ON "Patient_Custom"."pincosrc"="Global_Code_primIn"."code") LEFT OUTER JOIN "Custom_Dropdowns" "Custom_Dropdowns_Eth" ON "Patient_Custom"."ethnicity"="Custom_Dropdowns_Eth"."field_value") LEFT OUTER JOIN "Custom_Dropdowns" "Custom_Dropdowns_Phy" ON "Patient_Custom"."physical"="Custom_Dropdowns_Phy"."field_value"
 WHERE  "Global_Code_Sex"."category"='SEX' AND "Global_Code_Occu"."category"='OS' AND "Global_Code_Stud"."category"='SS' AND "Global_Code_Educ"."category"='ED' AND "Global_Code_LA"."category"='LA' AND "Global_Code_RS"."category"='RS' AND "Global_Code_Legal"."category"='CRTDISP' AND "Global_Code_primIn"."category"='PINCOSRC' AND "Global_Code_Race"."category"='R' AND "Global_Code_MStatus"."category"='MS' AND "Global_Code_Sal"."category"='SA'
 ORDER BY "Patient"."patient_id"

How can I write this to show all the data even if one of the fields are NULL. For example if Patient education is blank but everything else is there The patient's data is not pulling at all.

Thanks in advance.

 

[TanyaHarp]

For fields that can have null values I do a coalesce(field_name, default_null_value). The default null value can be set to text, numbers or just a blank

coalesce(field_name, "NULL")
coalesce(field_name, 0)
coalesce(field_name, "")

 

[mchambers]

Thanks but crystal gave me an error. I don't think you can use coalesce in a crystal formula.

 

[lbass]

The simplest solution would be to go to file->report options and check "convert nulls to default values".

-LB

 

[mchambers]

I found that as one of the solutions on this forum but when I ran the report still has nothing. ;(

 

[lbass]

I spoke too quickly. What records are you trying to return? Do you only want to see records where ALL categories are null? Or do you want to see records where ANY of these are null?

Do you know for sure that the category field is NOT null when the description field is null? E.g., if the Category = Sex, do you see "Sex" for category but no description field? Or are both missing?

-LB

 

[mchambers]

If I am understanding you correctly. All the category fields are Not null and neither is the description field. Though the actual SEX field in the Patient table maybe null if the data was never was entered for that patient.

I am trying to return all records regardless if they are Null or Not.
for example

patient_id	Name	        Sex	DOB	     Race
1001	    Testy Tester	Male	7/1/1965     white
1002	  Chester Tester	Male	1/21/1982   [COLOR=red]NULL[/color]

 

[lbass]

Okay, let's assume you UNchecked "convert nulls to default values". Then change your where clause to:

where
(
isnull({Patient.Sex}) or
{Global_Code_Sex.category}='SEX'
) and
(
isnull({Patient.occ_status}) or
{Global_Code_Occu.category}='OS'
) and
//etc.

-LB

 

[mchambers]

Oh boy, So I tried to do what you said and it does not like it. My database is not responding and crystal keeps shutting down.

Should I enter that code in the selection Expert? My sql query is crystal generated.

 

[lbass]

You could try this as a command (in a new report, enter the entire query above in the "Add command" area above the table list in the database expert) instead of using the select expert, and instead of using the where clause, add the criteria to the FROM clauses, like this:


LEFT OUTER JOIN "Global_Code" "Global_Code_Sex" ON "Patient"."sex"="Global_Code_Sex"."code" and
("Patient"."sex" is null or
"Global_Code_Sex"."category"='SEX'
)
)
LEFT OUTER JOIN "Global_Code" "Global_Code_Stud" ON "Patient"."student_status"="Global_Code_Stud"."code" and
(
"Patient"."student_status" is null or
"Global_Code_Stud"."category"='SS'
)
)

//etc.

-LB

 

[mchambers]

Ok I will try this. Thanks so much for you patience and time. I believe we will make this work.

 

[mchambers]

YAY it worked!!! You are awesome! Thanks so much for all your help!!