Hi,
I have this query which is returning the correct results
SELECT hdr.order_num
,hdr.business_unit_id
,dtl.item_class_id
FROM xxx.dd_ddhdr hdr
,xxx.dd_dddtl dtl
WHERE hdr.order_num = dtl.order_num
AND hdr.business_unit_id = dtl.business_unit_id
AND dtl.item_class_id NOT IN
(
SELECT cfl1.filtered_column_value
FROM sdds.ai_client_filter_list cfl1
WHERE cfl1.client_id = 'XXT'
AND cfl1.filtered_column_name = 'ITEM_SUBCLASS'
AND cfl1.business_unit_id = hdr.business_unit_id
)
AND hdr.order_datetime
BETWEEN to_date('3/28/2007 13:21','mm/dd/yyyy hh24:mi:ss')
AND to_date('3/28/2007 13:26','mm/dd/yyyy hh24:mi:ss');
I converted the above query to use NOT EXISTS instead of NOT IN as follows:
SELECT hdr.order_num
,hdr.business_unit_id
,dtl.item_class_id
FROM xxx.dd_ddhdr hdr
,xxx.dd_dddtl dtl
WHERE hdr.order_num = dtl.order_num
AND hdr.business_unit_id = dtl.business_unit_id
-- AND dtl.item_class_id NOT IN
AND NOT EXISTS
(
select 1
from sdds.ai_client_filter_list cfl1
where cfl1.client_id = 'XXT'
and cfl1.filtered_column_name = 'ITEM_SUBCLASS'
and hdr.business_unit_id = cfl1.business_unit_id
)
AND hdr.order_datetime
BETWEEN to_date('3/28/2007 13:21','mm/dd/yyyy hh24:mi:ss')
AND to_date('3/28/2007 13:26','mm/dd/yyyy hh24:mi:ss');
The 2nd query does not return any row after converting to NOT EXISTS.
Any idea to solve this?.
Thanks!