none
SQL join condition

    Question

  •      

    How can I rewrite following query to avoid join condition like this :
     SOM2.Address = SOM.ADDR_LINE1 || ' ' || ADDR_LINE2 ||' ' || CITY || ' ' || STATE || ' ' || ZIP_CODE

    Query:

    SEL 
    SOM.CUST_ACCT_KEY, 
    ORD_ID, 
    EQUIPDESC,
    SHIP_METH_DESC, 
    D_DESC, 
    SITE, 
    VEND_NAME, 
    ORD_DT, 
    CANCEL_DT,
    ADDR_LINE1 || ' ' || ADDR_LINE2 ||' ' || CITY || ' ' || STATE || ' ' || ZIP_CODE AS Address,
    AGENT_ID, AGENT_FULL_NAME  

    FROM SLS_ORD SOM
    JOIN

    (SEL CUST_ACCT_KEY,  
    ADDR_LINE1 || ' ' || ADDR_LINE2 ||' ' || CITY || ' ' || STATE || ' ' || ZIP_CODE AS Address 
    FROM
    SLS_ORD
    WHERE ORD_DT   BETWEEN  '2013-03-01' AND '2013-04-30'
    AND SHIP_METH_DESC NOT IN ('DO NOT SHIP')
    AND CANCEL_DT = '9999-11-17'
    GROUP BY 1, 2
     HAVING COUNT(*) >=6 ) SOM2

    ON SOM2.CUST_ACCT_KEY =  SOM.CUST_ACCT_KEY
     AND SOM2.Address = SOM.ADDR_LINE1 || ' ' || ADDR_LINE2 ||' ' || CITY || ' ' || STATE || ' ' || ZIP_CODE

    WHERE CANCEL_DT = '9999-11-17'
    AND ORD_DT   BETWEEN  '2013-03-01' AND '2013-04-30'
    AND SHIP_METH_DESC NOT IN ('DO NOT SHIP')



    Thanks.


    Tuesday, June 25, 2013 1:26 AM

Answers

  • The query you posted doesn't seem to be Transact-SQL (the topic of this forum) so I'm guessing you are using a DBMS other than SQL Server.  You'll probably get better answers on a forum appropriate for that database product.

    I think you can probably use a derived table for "SOM" just like you did for "SOM2".  That would allow you to alias the concatenated address fields as "Address" for the JOIN clause.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Tuesday, June 25, 2013 1:41 AM