I need a little help in determining what Join type/logic I need. I have the below data:
The column DISTRIBUTION_ACCOUNT_ID
is from the table I am Left joining to below (GL_CODE_COMBINATIONS
) and I want to match on the rows selected in green, which includes ones that have a value populated, as well as the NULL
ones. I also want to exclude rows where GL.SEGMENT3 <> 945
(This is the same rows that contains DISTRIBUTION_ACCOUNT_ID
61012 and 15000). The problem is that the Left outer join is not filtering out the top row with 61012, and if I switch the join to an INNER JOIN
then the logic to exclude GL.SEGMENT3 <> 945
works ok, but then it also filters out the blank rows below, which I still want.
(SELECT ABS(ROUND((SUM(PRIMARY_QUANTITY)/30),2)) AS BURN_RATE, TXN.ORGANIZATION_ID, TXN.INVENTORY_ITEM_ID,
TXN.SUBINVENTORY_CODE , TXN.DISTRIBUTION_ACCOUNT_ID, GL.SEGMENT3
FROM INV_MATERIAL_TXNS TXN
LEFT OUTER JOIN INV_TRANSACTION_TYPES_TL TYPE ON TYPE.TRANSACTION_TYPE_ID = TXN.TRANSACTION_TYPE_ID
LEFT OUTER JOIN GL_CODE_COMBINATIONS GL ON GL.CODE_COMBINATION_ID = TXN.DISTRIBUTION_ACCOUNT_ID
AND GL.SEGMENT3 <> 945
WHERE TRANSACTION_DATE >= TRUNC(SYSDATE-30)
AND TRANSACTION_DATE < TRUNC(SYSDATE)
AND TXN.INVENTORY_ITEM_ID = 100002040645155
GROUP BY TXN.ORGANIZATION_ID, TXN.INVENTORY_ITEM_ID, TXN.SUBINVENTORY_CODE , TXN.DISTRIBUTION_ACCOUNT_ID
, GL.SEGMENT3 )
If I switch GL_CODE_COMBINATIONS
to be an INNER JOIN
, then in the results below I filter out the 61012 and 15000 rows as I wanted, but then it also (undersiderly) then filters out the 3 blank rows above as you see below. How can I ensure I am still including the blanks, but filtering out the other two?
You need to sign in to view this answers
Leave feedback about this