I have a Crystal command file with 3 Select statements and 2 Unions. I have a NOT IN Select statement that works with each of the 3 Select statements autonomously.

When i attempt to add the NOT IN Select Statement to each of the three original Select statements that are unioned together my code fails to function.

Are there some specific rules about using NOT IN statements with unions? Suggestions?

SCRIPT BELOW. The not in statement is in the last union.

SELECT

ds.donor_sample_id,
NULL AS COMP_MAN_ID,
NULL AS COMPINV_ID


FROM Donor_Draw dd
LEFT JOIN Donor d on dd.donor_id=d.donor_id
LEFT JOIN Donor_Visit_Qual_Test dvqt on dd.visit_id=dvqt.visit_id
INNER JOIN Donor_Draw dd364 on dd.donor_id=dd364.donor_id
LEFT JOIN Donor_Unit du on dd.draw_id=du.draw_id
LEFT JOIN Donor_Sample ds on dd.draw_id=ds.draw_id

WHERE
floor(months_between(dd.draw_start_datetime,d.birthdate)/12)>=16.00 and
floor(months_between(dd.draw_start_datetime,d.birthdate)/12)<19.00 and
dd.actual_collection_type_cd IN ('SUR ALYX','ALYX','WB 480','WB ALLO','WB AUTO480','WB AUTOADJ','WB AUTOLOW','WB HH','WB THER') and
dd.draw_status_cd='C' and
dvqt.donor_qual_test_id='HEMGLB' and


TRUNC (dd364.draw_start_datetime,'J') >= TRUNC (PKG_SESSION.FNC_GET_SESSION_DATETIME,'J')- :DAYS_LKBK and

TRUNC (dd364.draw_start_datetime,'J') < TRUNC (PKG_SESSION.FNC_GET_SESSION_DATETIME,'J')- :DAYS_OLD and

dd364.actual_collection_type_cd IN ('SUR ALYX','ALYX','WB 480','WB ALLO','WB AUTO480','WB AUTOADJ','WB AUTOLOW','WB HH','WB THER') and
dd364.draw_status_cd='C'AND

PKG_SESSION.FNC_GET_SESSION_DATETIME - 1 < TRUNC(dd.draw_start_datetime, 'J') + :DAYS_OLD AND
          dd.draw_start_datetime <= PKG_SESSION.FNC_GET_SESSION_DATETIME



UNION

SELECT
ds.donor_sample_id,
NULL AS COMP_MAN_ID,
NULL AS COMPINV_ID

FROM Donor_Draw dd
LEFT JOIN Donor d on dd.donor_id=d.donor_id
LEFT JOIN Donor_Visit_Qual_Test dvqt on dd.visit_id=dvqt.visit_id
LEFT JOIN Donor_Unit du on dd.draw_id=du.draw_id
LEFT JOIN Donor_Sample ds on dd.draw_id=ds.draw_id

WHERE
floor(months_between(dd.draw_start_datetime,d.birthdate)/12)>=16.00 and
floor(months_between(dd.draw_start_datetime,d.birthdate)/12)<19.00 and
dd.actual_collection_type_cd IN ('SUR ALYX','ALYX','WB 480','WB ALLO','WB AUTO480','WB AUTOADJ','WB AUTOLOW','WB HH','WB THER') and
dd.draw_status_cd='C' and
dvqt.donor_qual_test_id='HEMGLB' and

((d.gender_cd='M' and dvqt.donor_qual_test_result>=13.0 and d.gender_cd='M' and dvqt.donor_qual_test_result<=13.5)
or
(d.gender_cd='F' and dvqt.donor_qual_test_result>=12.5 and d.gender_cd='F' and dvqt.donor_qual_test_result<=13.0))AND

PKG_SESSION.FNC_GET_SESSION_DATETIME - 1 < TRUNC(dd.draw_start_datetime, 'J') + :DAYS_OLD AND
          dd.draw_start_datetime <= PKG_SESSION.FNC_GET_SESSION_DATETIME





UNION

SELECT
ds.donor_sample_id,
NULL AS COMP_MAN_ID,
NULL AS COMPINV_ID

FROM Donor_Draw dd
LEFT JOIN Donor d on dd.donor_id=d.donor_id
LEFT JOIN Donor_Visit_Qual_Test dvqt on dd.visit_id=dvqt.visit_id
LEFT JOIN Donor_Unit du on dd.draw_id=du.draw_id
LEFT JOIN Donor_Sample ds on dd.draw_id=ds.draw_id

WHERE
floor(months_between(dd.draw_start_datetime,d.birthdate)/12)>=16.00 and
floor(months_between(dd.draw_start_datetime,d.birthdate)/12)<19.00 and
dd.actual_collection_type_cd IN ('SUR ALYX','ALYX') and
dd.draw_status_cd='C' and
dvqt.donor_qual_test_id='HEMGLB' and
d.gender_cd='F' AND

PKG_SESSION.FNC_GET_SESSION_DATETIME - 1 < TRUNC(dd.draw_start_datetime, 'J') + :DAYS_OLD AND
          dd.draw_start_datetime <= PKG_SESSION.FNC_GET_SESSION_DATETIME

AND
ds.donor_sample_id NOT IN
     (
     SELECT
     ds.donor_sample_id

     FROM Donor_Draw dd
     LEFT JOIN Donor d on dd.donor_id=d.donor_id
     LEFT JOIN Donor_Unit du on dd.draw_id=du.draw_id
     LEFT JOIN Donor_Sample ds on dd.draw_id=ds.draw_id
     LEFT JOIN Donor_Test_Order dto on ds.donor_sample_id=dto.donor_sample_id
     LEFT JOIN Donor_Test_Order_Item dtoi on dto.test_order_id=dtoi.test_order_id

     WHERE
     floor(months_between(dd.draw_start_datetime,d.birthdate)/12)>=16.00 and
     floor(months_between(dd.draw_start_datetime,d.birthdate)/12)<19.00 and
     dd.actual_collection_type_cd IN ('SUR ALYX','ALYX','WB 480','WB ALLO','WB      AUTO480','WB AUTOADJ','WB AUTOLOW','WB HH','WB THER') and
     dd.draw_status_cd='C' and
     dtoi.item_id='SICKLE' and

     PKG_SESSION.FNC_GET_SESSION_DATETIME - 1 < TRUNC
     (dd.draw_start_datetime, 'J') + :DAYS_OLD AND
          dd.draw_start_datetime <=      PKG_SESSION.FNC_GET_SESSION_DATETIME
     )

This script does work. When i attempt to add the NOT IN select statement to each of the Unions then it fails to function. This script is accessing an oracle database. The script is fuctioning within a BECS computer system. The :DAYS_LKBK :DAYS_OLD are parameters on an Extract file. The report fails stating that it is having a problem with the EXTRACT.

I hoping someone can tell me why associating the NOT IN statement with the three original select statements is not working when it works individually with each of them. I am a crsytal user learning to use Crystal Command files. This is the place i know to come for help.