Minus Oerator in Crystal 10.0

[kinvie]

I am using Crystal 10.0 to hit against the Oracle tables for our Electronic Health Record. I am looking at three different tables and only want patients who appear in the first table and not in either of the other two. SO if they have hypertention and do not have CAD or Diabetes they should appear on the report, if the have hypertension and diabetes or CAD I do not want them. I thought the minus operator would give me the correct patients but I am still getting patients with more than one diagnosis. Can any one look at my code and let me know what I am doing wrong?

SELECT "HTN_EXTRACT"."MEDRECNO", "HTN_EXTRACT"."PROV_LAST_NAME",
"HTN_EXTRACT"."DATEOFBIRTH", "HTN_EXTRACT"."PID",
"HTN_EXTRACT"."SEARCHNAME", "HTN_EXTRACT"."PROBLEM_CODE",
"HTN_EXTRACT"."RESPPROVID", "HTN_EXTRACT"."HOMELOCATION"
FROM "REPORT"."HTN_EXTRACT" "HTN_EXTRACT" WHERE
"HTN_EXTRACT"."HOMELOCATION"=1.27340792500229e+015

minus



SELECT "DIABETES_EXTRACT"."MEDRECNO", "DIABETES_EXTRACT"."PROV_LAST_NAME",
"DIABETES_EXTRACT"."DATEOFBIRTH", "DIABETES_EXTRACT"."PID",
"DIABETES_EXTRACT"."SEARCHNAME", "DIABETES_EXTRACT"."PROBLEM_CODE",
"DIABETES_EXTRACT"."RESPPROVID", "DIABETES_EXTRACT"."HOMELOCATION"
FROM "REPORT"."DIABETES_EXTRACT" "DIABETES_EXTRACT"
WHERE "DIABETES_EXTRACT"."HOMELOCATION"=1.27340792500229e+015

minus




SELECT "CAD_EXTRACT"."MEDRECNO", "CAD_EXTRACT"."PROV_LAST_NAME",
"CAD_EXTRACT"."DATEOFBIRTH", "CAD_EXTRACT"."PID",
"CAD_EXTRACT"."SEARCHNAME", "CAD_EXTRACT"."PROBLEM_CODE",
"CAD_EXTRACT"."RESPPROVID", "CAD_EXTRACT"."HOMELOCATION"
FROM "REPORT"."CAD_EXTRACT" "CAD_EXTRACT"
WHERE "CAD_EXTRACT"."HOMELOCATION"=1.27340792500229e+015 )

 

[Madawc]

I'm not familiar with Oracle, but since no one else has answered, I'll try.

I'd use a completely different method. Have left-outer links from the first table, separately to the other two. Group, since you may have multiple occurances for a single table-1 record. Suppress group header and details, show just the group footer.

Use a running total to count the two tables, using the HOMELOCATION"=1.27340792500229e+015 test. Suppress the group footer when the count is greater than zero.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

I'm not sure you can do this, but try:

SELECT "HTN_EXTRACT"."MEDRECNO", "HTN_EXTRACT"."PROV_LAST_NAME",
"HTN_EXTRACT"."DATEOFBIRTH", "HTN_EXTRACT"."PID",
"HTN_EXTRACT"."SEARCHNAME", "HTN_EXTRACT"."PROBLEM_CODE",
"HTN_EXTRACT"."RESPPROVID", "HTN_EXTRACT"."HOMELOCATION"
FROM "REPORT"."HTN_EXTRACT" "HTN_EXTRACT" WHERE
"HTN_EXTRACT"."HOMELOCATION"=1.27340792500229e+015

minus

(
SELECT "DIABETES_EXTRACT"."MEDRECNO", "DIABETES_EXTRACT"."PROV_LAST_NAME",
"DIABETES_EXTRACT"."DATEOFBIRTH", "DIABETES_EXTRACT"."PID",
"DIABETES_EXTRACT"."SEARCHNAME", "DIABETES_EXTRACT"."PROBLEM_CODE",
"DIABETES_EXTRACT"."RESPPROVID", "DIABETES_EXTRACT"."HOMELOCATION"
FROM "REPORT"."DIABETES_EXTRACT" "DIABETES_EXTRACT"
WHERE "DIABETES_EXTRACT"."HOMELOCATION"=1.27340792500229e+015

union

SELECT "CAD_EXTRACT"."MEDRECNO", "CAD_EXTRACT"."PROV_LAST_NAME",
"CAD_EXTRACT"."DATEOFBIRTH", "CAD_EXTRACT"."PID",
"CAD_EXTRACT"."SEARCHNAME", "CAD_EXTRACT"."PROBLEM_CODE",
"CAD_EXTRACT"."RESPPROVID", "CAD_EXTRACT"."HOMELOCATION"
FROM "REPORT"."CAD_EXTRACT" "CAD_EXTRACT"
WHERE "CAD_EXTRACT"."HOMELOCATION"=1.27340792500229e+015 )
)

-LB