record selection on OR Statement

[tnguyen315]

Hello I have a crystal report XI with a record selection formula like this:

(
(
(Isnull({ITBHFILE.CLOSE_DATE})) and
({@ClosedStatus} = true) and
({ITBHFILE.PATIENT_TYPE} in ["3","4","5"]) and
({@TBZone} = {?Zone})
)
OR
(
(Isnull({ITBHFILE.CLOSE_DATE})) and
({@ClosedStatus} = true) and
({ITBDFILE.DIRECTLY_OBSERVED} in ["1","2"]) and
({ITBHFILE.PATIENT_TYPE} in ["1","2"]) and
({@TBZone} = {?Zone})
)
)

I suppose I have 52 records without duplicate, however based on the formula above I have 66 records with some of it have duplicate more than 1 on the first part if ({ITBHFILE.PATIENT_TYPE} in ["3","4","5"]) and have ({ITBHFILE.PATIENT_TYPE} in ["1","2"]) in both 1 and 2, that's why it had duplicate records.

Based on the formula above I have 47 records on the first part (instead of 33) and 19 records on the second part (which is right)

However, if I run only first part or second part seperately then I have correct result

(
(Isnull({ITBHFILE.CLOSE_DATE})) and
({@ClosedStatus} = true) and
({ITBHFILE.PATIENT_TYPE} in ["3","4","5"]) and
({@TBZone} = {?Zone})
)

I have 33 records on this formula

Second part I have 19 records.

(
(Isnull({ITBHFILE.CLOSE_DATE})) and
({@ClosedStatus} = true) and
({ITBDFILE.DIRECTLY_OBSERVED} in ["1","2"]) and
({ITBHFILE.PATIENT_TYPE} in ["1","2"]) and
({@TBZone} = {?Zone})
)


So please help, I'm very appreciated. I don't know what's wrong when combining all 2 parts together.
Thanks in advance.

 

[fisheromacse]

maybe something like this:


(
(
(Isnull({ITBHFILE.CLOSE_DATE}) and
({@ClosedStatus} = true) and
({@TBZone} = {?Zone})
)
AND
(
({ITBHFILE.PATIENT_TYPE} in ["3","4","5"]) OR
(
({ITBDFILE.DIRECTLY_OBSERVED} in ["1","2"]) AND
({ITBHFILE.PATIENT_TYPE} in ["1","2"])
)
)
)

 

[tnguyen315]

Thanks fisheromase for your response.
It's missing 1 ")".
So This is what I have and no error:

(
(
(Isnull({ITBHFILE.CLOSE_DATE})) and
({@ClosedStatus} = true) and
({@TBZone} = {?Zone})
)
AND
(
({ITBHFILE.PATIENT_TYPE} in ["3","4","5"])
OR
(
({ITBDFILE.DIRECTLY_OBSERVED} in ["1","2"]) AND
({ITBHFILE.PATIENT_TYPE} in ["1","2"])
)
)
)

But I still have the same result...which is 66, instead of 52 because of some duplicate.

 

[lbass]

You could either use one of the queries in the main report and one in a subreport, or you could try using a command with a union all statement where you use one set of criteria in the first part of the union all, and the other in the other where clause. This would be the most efficient, but I'm unsure of whether you can translate your formulas to command syntax. If you are interested in this option, let me know and also show the content of your formulas and paste in the current "show SQL query".

-LB

 

[tnguyen315]

Thanks lbass,
Can you show me how?

I have some formula fields:
CloseStatus = (Isnull ({ITBHFILE.CLOSED_TO_SUPV}) Or {ITBHFILE.CLOSED_TO_SUPV} = "N")

TBZone:
If isnull({ITBHFILE.CASE_NO}) or ({ITBHFILE.CASE_NO}) = "" then "UNKNOWN
ZONE" else
If (trim({ITBHFILE.CASE_NO}) = "ZONE1" OR ({ITBHFILE.CASE_NO}) = "ZONE-1"
OR ({ITBHFILE.CASE_NO}) = "ZONE- 1" OR ({ITBHFILE.CASE_NO}) = "ZONE -1" OR
({ITBHFILE.CASE_NO}) = "ZONE 1") then "01" else
If (trim({ITBHFILE.CASE_NO}) = "ZONE2" OR ({ITBHFILE.CASE_NO}) = "ZONE-2"
OR ({ITBHFILE.CASE_NO}) = "ZONE- 2" OR ({ITBHFILE.CASE_NO}) = "ZONE -2" OR
({ITBHFILE.CASE_NO}) = "ZONE 2") then "02" else
If (trim({ITBHFILE.CASE_NO}) = "ZONE3" OR ({ITBHFILE.CASE_NO}) = "ZONE-3"
OR ({ITBHFILE.CASE_NO}) = "ZONE- 3" OR ({ITBHFILE.CASE_NO}) = "ZONE -3" OR
({ITBHFILE.CASE_NO}) = "ZONE 3") then "03" else
If (trim({ITBHFILE.CASE_NO}) = "ZONE4" OR ({ITBHFILE.CASE_NO}) = "ZONE-4"
OR ({ITBHFILE.CASE_NO}) = "ZONE- 4" OR ({ITBHFILE.CASE_NO}) = "ZONE -4" OR
({ITBHFILE.CASE_NO}) = "ZONE 4") then "04" else
If (trim({ITBHFILE.CASE_NO}) = "CITY PT") or Left ({ITBHFILE.CASE_NO},4)
= "CITY" then "CITY PATIENTS" else
If ({ITBHFILE.CASE_NO}) > "" then "OTHERS"

and a parameter field : Zone

Below "show SQL query:"

SELECT DISTINCT "MPFILE"."NAME_LAST", "MPFILE"."NAME_MID",
"MPFILE"."NAME_FIRST", "ITBHFILE"."PATIENT_TYPE", "MPFILE"."DOB",
"MPFILE"."SEX", "MPFILE"."REG_DATE", "ITBHFILE"."PATIENT_NO",
"ITBHFILE"."DISEASE", "ITBHFILE"."COMMENTS",
"MPFILE"."PRIMARY_CARE_PROVIDER_NAM", "ITBHFILE"."CLOSED_TO_SUPV",
"ITBHFILE"."CASE_NO", "ITBHFILE"."CLOSE_DATE", "ITBHFILE"."HISTORY_DATE",
"ITBDFILE"."DIRECTLY_OBSERVED"
FROM ("INSIGHT"."dbo"."MPFILE" "MPFILE" INNER JOIN
"INSIGHT"."dbo"."ITBHFILE" "ITBHFILE" ON
"MPFILE"."PATIENT_NO"="ITBHFILE"."PATIENT_NO") LEFT OUTER JOIN
"INSIGHT"."dbo"."ITBDFILE" "ITBDFILE" ON
"ITBHFILE"."PATIENT_NO"="ITBDFILE"."PATIENT_NO"
WHERE "ITBHFILE"."CLOSE_DATE" IS NULL AND
(("ITBHFILE"."PATIENT_TYPE"='3' OR "ITBHFILE"."PATIENT_TYPE"='4' OR
"ITBHFILE"."PATIENT_TYPE"='5') OR ("ITBDFILE"."DIRECTLY_OBSERVED"='1' OR
"ITBDFILE"."DIRECTLY_OBSERVED"='2') AND ("ITBHFILE"."PATIENT_TYPE"='1' OR "ITBHFILE"."PATIENT_TYPE"='2'))

 

[lbass]

Try something like this as a command:


SELECT DISTINCT "MPFILE"."NAME_LAST",
"MPFILE"."NAME_MID", "MPFILE"."NAME_FIRST",
"ITBHFILE"."PATIENT_TYPE", "MPFILE"."DOB",
"MPFILE"."SEX", "MPFILE"."REG_DATE",
"ITBHFILE"."PATIENT_NO", "ITBHFILE"."DISEASE",
"ITBHFILE"."COMMENTS",
"MPFILE"."PRIMARY_CARE_PROVIDER_NAM",
"ITBHFILE"."CLOSED_TO_SUPV", "ITBHFILE"."CASE_NO",
"ITBHFILE"."CLOSE_DATE", "ITBHFILE"."HISTORY_DATE",
"ITBDFILE"."DIRECTLY_OBSERVED"

FROM ("INSIGHT"."dbo"."MPFILE" "MPFILE"
INNER JOIN "INSIGHT"."dbo"."ITBHFILE" "ITBHFILE" ON "MPFILE"."PATIENT_NO"="ITBHFILE"."PATIENT_NO")
LEFT OUTER JOIN "INSIGHT"."dbo"."ITBDFILE" "ITBDFILE" ON "ITBHFILE"."PATIENT_NO"="ITBDFILE"."PATIENT_NO"

WHERE "ITBHFILE"."CLOSE_DATE" IS NULL AND
(
(
"ITBDFILE"."DIRECTLY_OBSERVED"='1' OR
"ITBDFILE"."DIRECTLY_OBSERVED"='2'
) AND
(
"ITBHFILE"."PATIENT_TYPE"='1' OR
"ITBHFILE"."PATIENT_TYPE"='2'
)
)
AND
(
"ITBHFILE"."CLOSED_TO_SUPV" IS NULL OR
"ITBHFILE"."CLOSED_TO_SUPV" = 'N'
) AND
(
(
(
"ITBHFILE"."CASE_NO" is null or
"ITBHFILE"."CASE_NO" = ''
) and
UCASE('{?TBZone}') = "UNKNOWN ZONE"
) OR
(
(
"ITBHFILE"."CASE_NO" LIKE '%CITY PT%' or
{fn Left ("ITBHFILE"."CASE_NO",4)} = 'CITY'
) and
ucase('{?TBZone}') = 'CITY PATIENTS'
) OR
(
{fn RIGHT("ITBHFILE"."CASE_NO",1)} = '0'||'{?TBZone}'
) or
(
"ITBHFILE"."CASE_NO" <> '' and
UCASE('{?TBZone}') = 'OTHERS'
)

union all

SELECT DISTINCT "MPFILE"."NAME_LAST",
"MPFILE"."NAME_MID", "MPFILE"."NAME_FIRST",
"ITBHFILE"."PATIENT_TYPE",
"MPFILE"."DOB", "MPFILE"."SEX", "MPFILE"."REG_DATE",
"ITBHFILE"."PATIENT_NO", "ITBHFILE"."DISEASE",
"ITBHFILE"."COMMENTS", "MPFILE"."PRIMARY_CARE_PROVIDER_NAM",
"ITBHFILE"."CLOSED_TO_SUPV", "ITBHFILE"."CASE_NO",
"ITBHFILE"."CLOSE_DATE", "ITBHFILE"."HISTORY_DATE",
null

FROM ("INSIGHT"."dbo"."MPFILE" "MPFILE"
INNER JOIN "INSIGHT"."dbo"."ITBHFILE" "ITBHFILE" ON "MPFILE"."PATIENT_NO"="ITBHFILE"."PATIENT_NO")

WHERE "ITBHFILE"."CLOSE_DATE" IS NULL AND
(
(
"ITBHFILE"."PATIENT_TYPE"='3'
OR "ITBHFILE"."PATIENT_TYPE"='4'
OR "ITBHFILE"."PATIENT_TYPE"='5'
) AND
(
"ITBHFILE"."CLOSED_TO_SUPV" IS NULL OR
"ITBHFILE"."CLOSED_TO_SUPV" = 'N'
) AND
(
(
(
"ITBHFILE"."CASE_NO" is null or
"ITBHFILE"."CASE_NO" = ''
) and
UCASE('{?TBZone}') = "UNKNOWN ZONE"
) OR
(
(
"ITBHFILE"."CASE_NO" LIKE '%CITY PT%' or
{fn Left ("ITBHFILE"."CASE_NO",4)} = 'CITY'
) and
ucase('{?TBZone}') = 'CITY PATIENTS'
) OR
(
{fn RIGHT("ITBHFILE"."CASE_NO",1)} = '0'||'{?TBZone}'
) or
(
"ITBHFILE"."CASE_NO" <> '' and
UCASE('{?TBZone}') = 'OTHERS'
)

Not sure I have all the parens correct and the syntax might not be quite right for your database.

-LB

 

[tnguyen315]

lbass,
Thanks for response. I'm so sorry to answer back so late. I just get back to work today.
The code in your answer, I don't know how to do as a command. You do mean that I just need to copy and paste in the "Show query SQL"? Can you tell me more?
Thank you very much for your help.

 

[lbass]

You need to create the command by opening a new blank report->select your datasource->add command (above the list of tables). You should create the parameter right in the command on the right hand side.

-LB

 

[tnguyen315]

Thanks lbass...
I'll do it
Again, thanks