Home » SQL & PL/SQL » SQL & PL/SQL » Left outer join with NVL and Where (Oracle 8.1.6i)
Left outer join with NVL and Where [message #433136] Mon, 30 November 2009 05:46 Go to next message
s1970302
Messages: 6
Registered: November 2009
Location: seoul
Junior Member

I 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'?
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    11010100004
11010100016   Y    11010100005

[Mod Edit to add code tags and (bit of) formatting

[Updated on: Mon, 30 November 2009 06:09] by Moderator

Report message to a moderator

Re: Left outer join with NVL and Where [message #433138 is a reply to message #433136] Mon, 30 November 2009 05:51 Go to previous message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
When posting code can you please use code tags - see the orafaq forum guide if you're not sure how.

A test case would help a lot here - create table statments and insert statements so we can recreate your data.

Also the full output would be usefull, your sample is missing at least one column.
Previous Topic: String to Number Problem
Next Topic: how to exclude SATURDAY AND SUNDAY in a string of DATES
Goto Forum:
  


Current Time: Sun Dec 04 12:39:16 CST 2016

Total time taken to generate the page: 0.17515 seconds