SQL QUERY PROBLEM WITH A INLINE VIEW

[r9ronaldo]

Hi,
I am having the following problem
I have the following query with an inline view,
Select C.CONT_INTERNAL_ID||C.CONT_NUM, C.CTAWDTYP_CD, TXT_TBC_VAL
from CONT C, (Select T.TXT_TBC_CDE, TXT_TBC_VAL from TTBC T
WHERE NUM_TBH_ID = 501) X
WHERE C.CTAWDTYP_CD = X.TXT_TBC_CDE (+)

I tried replacing the inline view with the following query
Select C.CONT_INTERNAL_ID||C.CONT_NUM, C.CTAWDTYP_CD, T.TXT_TBC_VAL from CONT C, TTBC T
WHERE ((T.NUM_TBH_ID = 501) AND C.CTAWDTYP_CD = T.TXT_TBC_CDE(+))
ORDER BY CONTRACT_KEY

but it's fetching me the rong num of rows.

Any help will be appreciated,
Thanking you in Anticipation
Nilesh

 

[Dagon]

No, what you've done won't work because of the (T.NUM_TBH_ID = 501) condition, which isn't subject to he outer join. You could try:

Select C.CONT_INTERNAL_ID||C.CONT_NUM, C.CTAWDTYP_CD, T.TXT_TBC_VAL from CONT C, TTBC T
WHERE T.NUM_TBH_ID (+) = 501 AND C.CTAWDTYP_CD = T.TXT_TBC_CDE(+)
ORDER BY CONTRACT_KEY

 

[r9ronaldo]

Thankyou Dagon, It helped me a lot, I really appreciate your help
Thankyou
R9ronaldo