Unioned Select statements and NOT IN
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22299
Printed Date: 04 May 2024 at 6:16am
Topic: Unioned Select statements and NOT IN
Posted By: cbaldwin
Subject: Unioned Select statements and NOT IN
Date Posted: 21 Apr 2017 at 9:17am
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
)
|
Replies:
Posted By: kevlray
Date Posted: 21 Apr 2017 at 9:38am
A couple of things. First off. You did not mention how your code fails to function (i.e., error message?). Second this forum is more for Crystal Reports questions and not SQL questions. Third you did not mention what is your data source (MS-SQL, Oracle, etc.). But otherwise it appears that the code should work (not sure what the :DAYS_LKBK :DAYS_OLD are, parameters?).
|
Posted By: cbaldwin
Date Posted: 21 Apr 2017 at 9:57am
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.
|
Posted By: cbaldwin
Date Posted: 21 Apr 2017 at 10:00am
Blood Establishment Computer System - FDA
BECS
|
|