• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 298
  • Last Modified:

FULL OUTER JOIN working like a LEFT OUTER JOIN ?

I have two tables with the same field and key layout...

The key structure is STORE_NUMBER, TRAN_DATE, TRAN_TYPE.

Here's the problem.  When I use the SQL as it is here, it omits data from the table aliased as B.  If I swap the two aliases (effectively changing the left side table), it changes the outcome.  The end result is what only partially what I need, as it is now acting like a LEFT OUTER JOIN instead of the true FULL OUTER JOIN (which in this case does not behave as I expect it to).

Can anyone shed some light on this for me?  Thanks...

Flynn
SELECT (CASE WHEN A.STORE_NUMBER <> '' THEN A.STORE_NUMBER ELSE B.STORE_NUMBER END) 
 ,(CASE WHEN A.END_DT <> '' THEN A.END_DT ELSE B.END_DT END) 
 , SUM(B.TRAN_AMT) 
 , SUM(A.TRAN_AMT) 
  FROM PS_PW_WPAY A FULL OUTER JOIN PS_PW_CSA B ON A.STORE_NUMBER = B.STORE_NUMBER 
   AND A.TRAN_DATE = B.TRAN_DATE 
   AND A.TRAN_TYPE = B.TRAN_TYPE 
 WHERE (A.TRAN_TYPE IN ('1','3','4') 
    OR B.TRAN_TYPE IN ('1','3','4')) 
   AND B.STORE_NUMBER <> ' ' 
  GROUP BY (CASE WHEN A.STORE_NUMBER <> '' THEN A.STORE_NUMBER ELSE B.STORE_NUMBER END) , (CASE WHEN A.END_DT <> '' THEN A.END_DT ELSE B.END_DT END)
ORDER BY 1,2

Open in new window

0
Flynnster
Asked:
Flynnster
2 Solutions
 
Daniel WilsonCommented:
The problem is the comparison to a constant:
AND B.STORE_NUMBER <> ' '

If B is providing no rows, the <> ' ' will fail ... b/c it neither equals nor does not equal that constant.  It's NULL.

Try:
AND (B.STORE_NUMBER <> ' '  OR B.STORE_NUMBER is NULL)
0
 
8080_DiverCommented:
As coded, you could get either the A.STORE_NUMBER or the B.STORE_NUMBER as a L.  However, you are testing the A.STORE_NUMBER for an empty string instead of a NULL in order to decide whether to use the A or B Store Number.  Similarly, you are testing the A.END_DT for an empty string instead of a NULL in order to decide whether to use the value from the A or B tables.

Try the following:
SELECT   STORE_NUMBER
             ,END_DT
             ,SUM_BTRAN
             ,SUM_ATRAN
FROM
(
SELECT   COALESCE(A.STORE_NUMBER, B.STORE_NUMBER) STORE_NUMBER
              ,COALESCE(A.END_DT, B.END_DT) AS End_Dt
              ,SUM(B.TRAN_AMT) SUM_BTRAN
              ,SUM(A.TRAN_AMT) SUM_ATRAN
FROM PS_PW_WPAY A
FULL OUTER JOIN PS_PW_CSA B
ON  A.STORE_NUMBER = B.STORE_NUMBER
    AND A.TRAN_DATE = B.TRAN_DATE
    AND A.TRAN_TYPE = B.TRAN_TYPE
WHERE A.TRAN_TYPE IN('1', '3', '4')
       OR B.TRAN_TYPE IN('1', '3', '4')
GROUP BY COALESCE(A.STORE_NUMBER, B.STORE_NUMBER) STORE_NUMBER
  		,COALESCE(A.END_DT, B.END_DT) AS End_Dt
) Z
ORDER BY STORE_NUMBER, End_Dt

Open in new window

0
 
TempDBACommented:
As Daniel pointed out, the comparison with blank ('') is causing the error. Check this out.
The following query returns 1 though it looks like it should return false

select case when '     ' = '' then 1 else 0 end

White spaces without characters (visible) on either side are not allocated a character byte so it is still considered a ‘ ’ match. You can try this instead

select case when '<tab space>' = '' then 1 else 0 end

This would return 0. So, just remove the check or you can do that with comparing lenth. in where filter

len(B.STORE_NUMBER ) <> 0 instead of
B.STORE_NUMBER <> ' '

Following are supporting test.
DECLARE @abc VARCHAR(100)
SELECT LEN(@abc)

SET @abc = ''
SELECT LEN(@abc)

SET @abc = 'as'
SELECT LEN(@abc)

SET @abc = NULL
SELECT LEN(@abc)


0
 
FlynnsterAuthor Commented:
Thanks!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now