Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Convert NOT IN to NOT EXISTS if possible

user1980Sep 14 2009 — edited Sep 14 2009
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!
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 12 2009
Added on Sep 14 2009
1 comment
498 views