Friday, February 10, 2012

Concatenating in Left Join Query

I need to create a concatenated field based on both sides of a LEFT OUTER
JOIN. When I tried this, I got all nulls in my resultset for that field.
What I wanted was whatever is in the left side concatenated with nothing if
the right side doesn't exist. How can I achieve this. My current SQL is:
Open Order is the problematic field.
<code>
SELECT O.COMPANY, O.CUSTOMER, S.SHIP_TO, A.ACTIVE_STATUS,
A.SEARCH_NAME, A.CURR_BAL, A.OPEN_ORDS, O.PO_REQ_FL, O.CIA_FL, O.CIA_PCT,
B.SHP_USR_FLD_01 + B.SHP_USR_FLD_02 AS ATTENTION,
B.SHP_USR_FLD_03 + B.SHP_USR_FLD_04 AS ORDER_BY,
S.ADDR3 + B.SHP_USR_FLD_05 AS ORDER_EMAIL
FROM SHIPTO S
LEFT OUTER JOIN BLSHPUF B
ON B.COMPANY = S.COMPANY
AND B.CUSTOMER = S.CUSTOMER
AND B.SHIP_TO = S.SHIP_TO
INNER JOIN OECUST O
ON O.COMPANY = S.SHIP_TO
AND O.CUSTOMER = S.CUSTOMER
INNER JOIN ARCUSTOMER A
ON A.COMPANY = S.COMPANY
AND A.CUSTOMER = S.CUSTOMER
</code>
--
-hexaUse IsNull() function Or Coalesce() function
AS In:
SELECT O.COMPANY, O.CUSTOMER, S.SHIP_TO, A.ACTIVE_STATUS,
A.SEARCH_NAME, A.CURR_BAL, A.OPEN_ORDS, O.PO_REQ_FL, O.CIA_FL, O.CIA_PCT,
B.SHP_USR_FLD_01 + IsNull(B.SHP_USR_FLD_02, '') AS ATTENTION,
B.SHP_USR_FLD_03 + IsNull(B.SHP_USR_FLD_04, '') AS ORDER_BY,
S.ADDR3 + IsNull(B.SHP_USR_FLD_05, '') AS ORDER_EMAIL
FROM SHIPTO S
LEFT JOIN BLSHPUF B
ON B.COMPANY = S.COMPANY
AND B.CUSTOMER = S.CUSTOMER
AND B.SHIP_TO = S.SHIP_TO
INNER JOIN OECUST O
ON O.COMPANY = S.SHIP_TO
AND O.CUSTOMER = S.CUSTOMER
INNER JOIN ARCUSTOMER A
ON A.COMPANY = S.COMPANY
AND A.CUSTOMER = S.CUSTOMER
"hexa" wrote:

> I need to create a concatenated field based on both sides of a LEFT OUTER
> JOIN. When I tried this, I got all nulls in my resultset for that field.
> What I wanted was whatever is in the left side concatenated with nothing i
f
> the right side doesn't exist. How can I achieve this. My current SQL is
:
> Open Order is the problematic field.
> <code>
> SELECT O.COMPANY, O.CUSTOMER, S.SHIP_TO, A.ACTIVE_STATUS,
> A.SEARCH_NAME, A.CURR_BAL, A.OPEN_ORDS, O.PO_REQ_FL, O.CIA_FL, O.CIA_PCT
,
> B.SHP_USR_FLD_01 + B.SHP_USR_FLD_02 AS ATTENTION,
> B.SHP_USR_FLD_03 + B.SHP_USR_FLD_04 AS ORDER_BY,
> S.ADDR3 + B.SHP_USR_FLD_05 AS ORDER_EMAIL
> FROM SHIPTO S
> LEFT OUTER JOIN BLSHPUF B
> ON B.COMPANY = S.COMPANY
> AND B.CUSTOMER = S.CUSTOMER
> AND B.SHIP_TO = S.SHIP_TO
> INNER JOIN OECUST O
> ON O.COMPANY = S.SHIP_TO
> AND O.CUSTOMER = S.CUSTOMER
> INNER JOIN ARCUSTOMER A
> ON A.COMPANY = S.COMPANY
> AND A.CUSTOMER = S.CUSTOMER
> </code>
> --
> -hexa|||Thanks, Coalesce is what I needed. My revised query is:
SELECT S.COMPANY, S.CUSTOMER, S.SHIP_TO, A.ACTIVE_STATUS, A.SEARCH_NAME,
A.CURR_BAL, A.OPEN_ORDS, A.TAX_EXEMPT_CD, O.PO_REQ_FL, O.CIA_FL, O.CIA_PCT,
B.SHP_USR_FLD_01 + B.SHP_USR_FLD_02 AS ATTENTION, B.SHP_USR_FLD_03 +
B.SHP_USR_FLD_04 as ORDER_BY,
COALESCE(S.ADDR3 + B.SHP_USR_FLD_05,S.ADDR3,'') AS ORDER_EMAIL,
A1.CUST_USER8 AS TAX_FORM_DT
FROM SHIPTO S
LEFT OUTER JOIN BLSHPUF B
ON B.COMPANY = S.COMPANY
AND B.CUSTOMER = S.CUSTOMER
AND B.SHIP_TO = S.SHIP_TO
INNER JOIN ARCUSTOMER A
ON A.COMPANY = S.COMPANY
AND A.CUSTOMER = S.CUSTOMER
INNER JOIN OECUST O
ON O.COMPANY = S.COMPANY
AND O.CUSTOMER = S.CUSTOMER
INNER JOIN ARCUSTFLDS A1
ON A1.COMPANY = S.COMPANY
AND A1.CUSTOMER = S.CUSTOMER
"CBretana" wrote:
> Use IsNull() function Or Coalesce() function
> AS In:
> SELECT O.COMPANY, O.CUSTOMER, S.SHIP_TO, A.ACTIVE_STATUS,
> A.SEARCH_NAME, A.CURR_BAL, A.OPEN_ORDS, O.PO_REQ_FL, O.CIA_FL, O.CIA_PCT
,
> B.SHP_USR_FLD_01 + IsNull(B.SHP_USR_FLD_02, '') AS ATTENTION,
> B.SHP_USR_FLD_03 + IsNull(B.SHP_USR_FLD_04, '') AS ORDER_BY,
> S.ADDR3 + IsNull(B.SHP_USR_FLD_05, '') AS ORDER_EMAIL
> FROM SHIPTO S
> LEFT JOIN BLSHPUF B
> ON B.COMPANY = S.COMPANY
> AND B.CUSTOMER = S.CUSTOMER
> AND B.SHIP_TO = S.SHIP_TO
> INNER JOIN OECUST O
> ON O.COMPANY = S.SHIP_TO
> AND O.CUSTOMER = S.CUSTOMER
> INNER JOIN ARCUSTOMER A
> ON A.COMPANY = S.COMPANY
> AND A.CUSTOMER = S.CUSTOMER
> "hexa" wrote:
>|||IsNull() is the SAME as Coalesce(). except Coalesce takes an arbitrary
number of arguments, but IsNull() only takes 2 arguments...
So for 2 Arguments, they're the same...
"hexa" wrote:
> Thanks, Coalesce is what I needed. My revised query is:
> SELECT S.COMPANY, S.CUSTOMER, S.SHIP_TO, A.ACTIVE_STATUS, A.SEARCH_NAME,
> A.CURR_BAL, A.OPEN_ORDS, A.TAX_EXEMPT_CD, O.PO_REQ_FL, O.CIA_FL, O.CIA_P
CT,
> B.SHP_USR_FLD_01 + B.SHP_USR_FLD_02 AS ATTENTION, B.SHP_USR_FLD_03 +
> B.SHP_USR_FLD_04 as ORDER_BY,
> COALESCE(S.ADDR3 + B.SHP_USR_FLD_05,S.ADDR3,'') AS ORDER_EMAIL,
> A1.CUST_USER8 AS TAX_FORM_DT
> FROM SHIPTO S
> LEFT OUTER JOIN BLSHPUF B
> ON B.COMPANY = S.COMPANY
> AND B.CUSTOMER = S.CUSTOMER
> AND B.SHIP_TO = S.SHIP_TO
> INNER JOIN ARCUSTOMER A
> ON A.COMPANY = S.COMPANY
> AND A.CUSTOMER = S.CUSTOMER
> INNER JOIN OECUST O
> ON O.COMPANY = S.COMPANY
> AND O.CUSTOMER = S.CUSTOMER
> INNER JOIN ARCUSTFLDS A1
> ON A1.COMPANY = S.COMPANY
> AND A1.CUSTOMER = S.CUSTOMER
>
> "CBretana" wrote:
>

No comments:

Post a Comment