How to mimic 'NOT EXISTS' in Crystal Reports 9

[jejohn1216]

I have a sql statement that selects the proper data by using a NOT EXISTS sub select. Runs fine and is accurate using Query Analyzer in SQL Server. I have tried to build a report in CR that mimics this select, but cannot get it to match. I tried using "not ({TableA.FieldF} in [109, 92])" but get too many rows of data.

My working SQL select is:
SELECT DISTINCT
CONVERT(CHAR(8),A.Client)
, CONVERT(CHAR(12),R.res_last_name)
, CONVERT(CHAR(12),R.res_first_name)
, CONVERT(CHAR(5),R.res_middle_name)
, R.res_status_code AS STATUS
, R.curr_visit_type AS TYPE
, R.curr_room_nbr AS ROOM
, R.curr_bed AS BED
, CONVERT(CHAR(11), A.CreateDate, 121)
FROM owner1.TableR R INNER JOIN
owner1.TableX A ON R.res_snbr = A.Client
WHERE ((R.curr_discharge_dt IS NULL)
AND (A.CreateDate = CONVERT(CHAR(11), GETDATE(),121))
AND (R.curr_visit_type = 'I')
AND (R.res_status_code = 'A'))
AND NOT EXISTS (select 'X' from owner1.TableX A2
where A2.HierarchyID IN ('92', '109')
and A2.Client = A.Client
and A2.CreateDate = CONVERT(CHAR(11), GETDATE(),121))

My Crystal SQL comes out as:

SELECT DISTINCT "TableR"."res_snbr", "TableR"."res_last_name", "TableR"."res_first_name", "TableR"."curr_room_nbr", "TableR"."curr_bed", "TableX"."HierarchyID", "TableX"."CreateDate", "TableX"."CreateTime", "TableR"."res_status_code", "TableR"."curr_visit_type", "TableR"."curr_discharge_dt", "Facility"."fac_name"
FROM ("db9x"."owner1"."Facility" "Facility" INNER JOIN "db9x"."owner1"."TableR" "TableR" ON "Facility"."fac_code"="TableR"."curr_fac_code") INNER JOIN "db9x"."owner1"."TableX" "TableX" ON "TableR"."res_snbr"="TableX"."Client"
WHERE "TableR"."curr_discharge_dt" IS NULL AND ("TableX"."CreateDate">={ts '2006-05-03 00:00:00'} AND "TableX"."CreateDate"<{ts '2006-05-04 00:00:00'}) AND "TableR"."res_status_code"='A' AND "TableR"."curr_visit_type"='I' AND NOT ("TableX"."HierarchyID"=92 OR "TableX"."HierarchyID"=109)
ORDER BY "TableR"."res_snbr"

TIA,

jejohn1216

 

[synapsevampire]

You can paste your SQL in as a data source and use parameters there, use the Add Command below the data source to do so.

btw, NOT EXISTS tend to be slow, I would opt to use a derived table and reverse the filter and use an inner join, it'll probably prove faster. Check with the SQL Server folk in that forum though.

-k

 

[jejohn1216]

OK, forgive my dense-ness here.

If I open the CR I created and go to Database -> Set Datasource Location ...... is that where I use the SQL as a new data source? My current connection is ADO to the database. What type would the SQL statement be? When I click on Create New Connection, I don't have an Add Command option.

Thanks,

jej1216

 

[synapsevampire]

Don't use ADO, use ODBC or OLE DB, the first thing listed under either is Add Command.

You can paste SQL and apply parameters there.

-k