SQL rewrite

Database
Enthusiast

SQL rewrite

How can I rewrite following query to avoid join condition like this :

AND 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.

Tags (3)
1 REPLY

Re: SQL rewrite

you can write like this

SELECT 

SEL

SOM.CUST_ACCT_KEY, 

SOM.ORD_ID,

SOM.EQUIPDESC,

SOM.SHIP_METH_DESC, 

SOM.D_DESC, 

SOM.SITE, 

SOM.VEND_NAME, 

SOM.ORD_DT, 

SOM.CANCEL_DT,

SOM.Address,

SOM.AGENT_ID, SOM.AGENT_FULL_NAME  

FROM (SEL 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 

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')) 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.Address