ethanjohnsons
asked on
sub-query
SELECT
O.ORDER_MNEMONIC,
EA.ALIAS,
E.ACTIVE_STATUS_DT_TM,
P.NAME_FULL_FORMATTED,
P.POSITION_CD
FROM
EKS_DLG_EVENT E,
PRSNL P,
ENCNTR_ALIAS EA,
ORDERS O
WHERE
bla...for join ...
E.DLG_NAME = 'MUL_MED!DRUGFOOD'
AND E.DLG_PRSNL_ID = P.PERSON_ID
AND E.ACTIVE_STATUS_DT_TM BETWEEN '7 days ago' AND 'today'
AND P.POSITION_CD NOT IN (9620245, 9616239, 9616238)
AND E.ENCNTR_ID = EA.ENCNTR_ID AND EA.ENCNTR_ALIAS_TYPE_CD = 141.00
AND E.TRIGGER_ORDER_ID = O.ORDER_ID
O.ORDER_MNEMONIC and EA.ALIAS on SELECT can be identical whereas the others on SELECT can be different.
If the identical records with the same O.ORDER_MNEMONIC and EA.ALIAS ever exit before (i.e. 1 year ago, 1 month ago, etc),
any records after the first one should not be qualified (= should not be selected).
For instance,
ABC 1234567890 01/01/2005 John Doe DBA
ABC 1234567890 07/04/2005 Jane Doe DBA
-> these two records have the same ABC 1234567890 (O.ORDER_MNEMONIC and EA.ALIAS), so
only the 1st record should be qualified when the report is run.
There can be another record like:
ABC 1234567890 07/19/2006 Lisa Doe DBA
but, this record should not be selected even though this recod on 07/19/2006 meets all of the qualifications on WHERE clause
BECAUSE (ABC 1234567890 01/01/2005 John Doe DBA) exists.
It seems like subquery is to be used....
any ideas?
O.ORDER_MNEMONIC,
EA.ALIAS,
E.ACTIVE_STATUS_DT_TM,
P.NAME_FULL_FORMATTED,
P.POSITION_CD
FROM
EKS_DLG_EVENT E,
PRSNL P,
ENCNTR_ALIAS EA,
ORDERS O
WHERE
bla...for join ...
E.DLG_NAME = 'MUL_MED!DRUGFOOD'
AND E.DLG_PRSNL_ID = P.PERSON_ID
AND E.ACTIVE_STATUS_DT_TM BETWEEN '7 days ago' AND 'today'
AND P.POSITION_CD NOT IN (9620245, 9616239, 9616238)
AND E.ENCNTR_ID = EA.ENCNTR_ID AND EA.ENCNTR_ALIAS_TYPE_CD = 141.00
AND E.TRIGGER_ORDER_ID = O.ORDER_ID
O.ORDER_MNEMONIC and EA.ALIAS on SELECT can be identical whereas the others on SELECT can be different.
If the identical records with the same O.ORDER_MNEMONIC and EA.ALIAS ever exit before (i.e. 1 year ago, 1 month ago, etc),
any records after the first one should not be qualified (= should not be selected).
For instance,
ABC 1234567890 01/01/2005 John Doe DBA
ABC 1234567890 07/04/2005 Jane Doe DBA
-> these two records have the same ABC 1234567890 (O.ORDER_MNEMONIC and EA.ALIAS), so
only the 1st record should be qualified when the report is run.
There can be another record like:
ABC 1234567890 07/19/2006 Lisa Doe DBA
but, this record should not be selected even though this recod on 07/19/2006 meets all of the qualifications on WHERE clause
BECAUSE (ABC 1234567890 01/01/2005 John Doe DBA) exists.
It seems like subquery is to be used....
any ideas?
ASKER
why do you put "<" on " AND Px.NAME_FULL_FORMATTED < P.NAME_FULL_FORMATTED"?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
..
AND NOT EXISTS (
SELECT *
FROM EKS_DLG_EVENT Ex, -- NOTE: This should really use the INNER JOIN / LEFT JOIN / RIGHT JOIN syntax, not the JOIN in the WHERE clause.
PRSNL Px,
ENCNTR_ALIAS EAx,
ORDERS Ox
WHERE
Ox.ORDER_MNEMONIC = O.ORDER_MNEMONIC
AND EAx.ALIAS = EA.ALIAS
AND (
Ex.ACTIVE_STATUS_DT_TM < E.ACTIVE_STATUS_DT_TM
OR (
Ex.ACTIVE_STATUS_DT_TM = E.ACTIVE_STATUS_DT_TM
AND Px.NAME_FULL_FORMATTED < P.NAME_FULL_FORMATTED
)
)
AND --->> bla...for join ...
AND E.DLG_NAME = 'MUL_MED!DRUGFOOD'
AND E.DLG_PRSNL_ID = P.PERSON_ID
AND E.ACTIVE_STATUS_DT_TM BETWEEN '7 days ago' AND 'today'
AND P.POSITION_CD NOT IN (9620245, 9616239, 9616238)
AND E.ENCNTR_ID = EA.ENCNTR_ID
AND EA.ENCNTR_ALIAS_TYPE_CD = 141.00
AND E.TRIGGER_ORDER_ID = O.ORDER_ID
)