Results 1 to 3 of 3
  1. #1
    NSearch is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    62

    Replacing "NOT IN" with Exclusion Join

    Hi all,



    I am hoping someone can help me with this query. The design view will not display the structure because of the complexity in the IIF statements.

    The area I am having trouble with is NOT IN statement. I would like to remove that piece and have a much faster join, but I am not sure how to join on all elements EXCEPT for the ones not desired.

    I have the session id for a set number of batch ids and the batch ids should be excluded if they were the last to be processed by a particular process location.

    Code:
    SELECT Count(*) AS Number_of_Duplicates,        sd.charge_category_detail_code, 
           sd.tracking_number, 
           sd.invoice_number, 
           sd.account_number, 
           sd.net_amount, 
           sd.billed_weight, 
           sd.invoice_date, 
           sd.transaction_date, 
           sd.pickup_record_number, 
           sd.receiver_name, 
           sd.receiver_company_name, 
           sd.receiver_address_line_1, 
           sd.receiver_address_line_2, 
           sd.receiver_city, 
           sd.receiver_state, 
           sd.receiver_postal, 
           sd.receiver_country_territory 
    FROM   shipment_details AS sd 
           INNER JOIN tracking_history 
                   ON ( Iif(Isnull(sd.receiver_country_territory), "", 
                        sd.receiver_country_territory) 
                                   = 
    Iif(Isnull(tracking_history.receiver_country_territory), 
    "", 
      tracking_history.receiver_country_territory) ) 
    AND ( Iif(Isnull(sd.receiver_postal), "", sd.receiver_postal) = 
    Iif(Isnull(tracking_history.receiver_postal), "", 
    tracking_history.receiver_postal) ) 
    AND ( Iif(Isnull(sd.receiver_state), "", sd.receiver_state) = 
    Iif(Isnull(tracking_history.receiver_state), "", 
    tracking_history.receiver_state) ) 
    AND ( Iif(Isnull(sd.receiver_city), "", sd.receiver_city) = 
    Iif(Isnull(tracking_history.receiver_city), "", 
    tracking_history.receiver_city) ) 
    AND ( Iif(Isnull(sd.receiver_address_line_2), "", 
    sd.receiver_address_line_2) = 
    Iif( 
    Isnull(tracking_history.receiver_address_line_2), "", 
    tracking_history.receiver_address_line_2) ) 
    AND ( Iif(Isnull(sd.receiver_address_line_1), "", 
    sd.receiver_address_line_1) = 
    Iif( 
    Isnull(tracking_history.receiver_address_line_1), "", 
    tracking_history.receiver_address_line_1) ) 
    AND ( Iif(Isnull(sd.receiver_company_name), "", 
    sd.receiver_company_name) = 
    Iif(Isnull(tracking_history.receiver_company_name), "", 
    tracking_history.receiver_company_name) ) 
    AND ( Iif(Isnull(sd.receiver_name), "", sd.receiver_name) = 
    Iif(Isnull(tracking_history.receiver_name), "", 
    tracking_history.receiver_name) ) 
    AND ( sd.tracking_number = tracking_history.tracking_number ) 
    AND ( sd.invoice_number = tracking_history.invoice_number ) 
    AND ( sd.account_number = tracking_history.account_number ) 
    WHERE  sd.charge_classification_code = 'FRT' 
           AND sd.bill_option_code NOT IN ( 'DTP', 'DFC' ) 
           AND ( sd.charge_category_code = 'SHP' 
                  OR sd.charge_category_detail_code = 'CLB' ) 
           AND sd.tracking_number IS NOT NULL 
           AND sd.batch_id NOT IN (SELECT batch_id 
                                   FROM   process_log 
                                   WHERE  session_id IN (SELECT session_id 
                                                         FROM   process_log 
                                                         WHERE  process_end = 
                                          (SELECT Max(process_end) 
                                           FROM   process_log) 
                                          AND process_location = ( 
                                              SELECT 
                                              workspace_name 
                                              FROM 
                                              local_information)) 
                                  ) 
    GROUP  BY sd.charge_category_detail_code, 
              sd.tracking_number, 
              sd.invoice_number, 
              sd.account_number, 
              sd.net_amount, 
              sd.billed_weight, 
              sd.invoice_date, 
              sd.transaction_date, 
              sd.pickup_record_number, 
              sd.receiver_name, 
              sd.receiver_company_name, 
              sd.receiver_address_line_1, 
              sd.receiver_address_line_2, 
              sd.receiver_city, 
              sd.receiver_state, 
              sd.receiver_postal, 
              sd.receiver_country_territory;

  2. #2
    NSearch is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    62
    How do you delete a post?

    I have decided to go a different direction and stick with the batch_id being the unique value vs chasing my tail and creating another id (session_id) for really no good reason.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    you can simplify your iif statement from
    Code:
    ( Iif(Isnull(sd.receiver_country_territory), "",sd.receiver_country_territory) =Iif(Isnull(tracking_history.receiver_country_territory),"", tracking_history.receiver_country_territory) )
    to

    nz(sd.receiver_country_territory, "")=nz(tracking_history.receiver_country_territory ,"")
    you can also just do this as a criteria to a cartesian query

    but do you really want records with a null value in sd.receiver_country_territory to bring every record in tracking_history with a null value in the related field (subject to the other joins and criteria)?

    you could also ensure all nulls in the tables are replaced with zls which not only would simplify your code but no doubt make the query significantly faster which is one of your objectives.

    With regards the subquery, it looks to me like it would stand on its own as a separate query or virtual table which you can left join to.

    I presume you are trying to identify entries already entered. You might want to consider using a hash function to hash the relevant fields and then simply join on the hash values

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 10-19-2016, 03:40 AM
  2. Searching and replacing "null" values
    By biederboat in forum Access
    Replies: 20
    Last Post: 07-19-2016, 12:49 PM
  3. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  4. Replies: 3
    Last Post: 05-15-2013, 02:49 PM
  5. Replies: 0
    Last Post: 09-17-2009, 12:21 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums