left outer join null value problem in condition
717604Nov 30 2009 — edited Jan 14 2010I have the two table that can be left outer join but result is not what I expected.
The 'BOM_YM' colume have null value. Can you give me the reason?
It should be all 'Y' if keyvalue is existed?
This is query
select *
FROM human.TBL_ITEMCODE A,
(SELECT KEYVALUE, 'Y' AS BOM_YN
FROM human.TBL__SYSTREE__
WHERE KIND = '1'
AND PHANDLE = '0'
) B
WHERE A.CODE_ITEM = B.KEYVALUE(+)
and A.ITEM_KIND IN ('1','2')
AND NVL(A.USE_YN,'N') = 'Y'
ORDER BY 1;
and and the result is like
CODE_ITEM USE_YN KEYVALUE BOM_YM
11010100012 Y 11010100001 Y
11010100013 Y 11010100002 Y
11010100014 Y 11010100003 Y
11010100015 Y
11010100016 Y 11010100005