I'm doing a left outer join and only getting back matching rows like it was an inner join.
To simplify the data, my first table(ROW_SEG), or left table looks something like this:
ASN | DEPT NO
-----------------------
85 | 836
86 | null
87 | null
My second table(RF_MERCHANT_ORG) has DEPT_NAME, and some other things which i want to get when i have a dept number.
DEPT NO | DEPT_NAME
-----------------------
836 | some dept name 1
837 | some dept name 2
838 | some dept name 3
In this case after my join i'd only get 1 row, for ASN 85 that had a DEPT NO.
...omitting a bunch of SQL for simplicity
, ROW_SEG AS (
SELECT *
FROM VE_SI_EC_OI
WHERE ROW_NUM BETWEEN 1 AND 1000 -- screen pagination, hardcoding values
)
-- ROW_SEG has a count of 1000 now
, RFS_JOIN AS (
SELECT ROW_SEG.*
,MO.BYR_NO
,MO.BYR_NAME
,MO.DEPT_NAME
FROM ROW_SEG
LEFT OUTER JOIN RF_MERCHANT_ORG MO
ON ROW_SEG.DEPT_NO = MO.DEPT_NO
WHERE MO.ORG_NO = 100
)
SELECT * FROM RFS_JOIN; -- returns less than 1000
I only get back the number of rows equal to the number of rows that have dept nos. So in my little data example above i would only get 1 row for ASN 85, but i want all rows with BYR_NO, BYR_NAME, AND DEPT_NAME populated on rows where i had a DEPT_NO, and if not, then empty/null columns.
If ORG_NO is within the RF_MERCHANT_ORG table (using aliases consistently would help there) then acting like an inner join would then would be the correct result for the SQL being used.
The join should be this to make it act like a proper left join:
LEFT OUTER JOIN RF_MERCHANT_ORG MO ON ROW_SEG.DEPT_NO = MO.DEPT_NO AND MO.ORG_NO = 100
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments