Hi, i really hope i can get help

[sommererdbeere]

Hi,

i really hope i can get help from all of the experts here.. i've looking into this , but doesn't seems to have any luck.. i donno if it is syntax problem.. many many many thanks..

i use Oracle 8.0, debug in TOAD

here is my select statement:

select
ip.LOCATION_NO location_no,
ip.PART_NO part_no,
ip.qty_after qty_after,
cr.QTY_COUNTED qty_count,
cr.QTY_ONHAND qty_onhand
from INVENTORY ip, COUNT cr where
cr.LOCATION_NO = ip.LOCATION_NO and
cr.part_no(+) = ip.part_no

data:
location_no part_no qty_after qty_count qty_onhand
hk 1550ik 0 4400 4700
hk 1550ik 0 4400 4700
hk 1550ik 5005 4700 4800
hk 1550ik 9000 4400 4000
hk 1550ik 7879 4700 4000

my problem here is i want to '0' out or 'Noshow' qty_count and qty_onhand column when this condition is valid, cr.part_no(+) = ip.part_no.. meaning when cr.part_no contains no record and ip.part_no contains record, '0' out or 'Noshow' qty_count and qty_onhand column

i've try to use IF statement after Where clause and
nvl(cr.QTY_COUNTED, '0') qty_count,
nvl(cr.QTY_ONHAND, '0') qty_onhand
to replace cr.QTY_COUNTED and cr.QTY_ONHAND, but none of them works..

i cannot depend on qty_after, because there are data that are not '0'(unlike the example above). I just want to take the action before merge/join occurs, cr.part_no(+) = ip.part_no?

do u have any idea on how to approach??? is it true i should use return code in oracle???

please help.. many many thanks..

m

 

[lewisp]

Your approch to NVL the columns from COUNT is correct, but you are missing an outer join statement. If you want to outer join a table you must apply it to all the columns you are joining on.

[tt]select
ip.LOCATION_NO location_no,
ip.PART_NO part_no,
ip.qty_after qty_after,
cr.QTY_COUNTED qty_count,
cr.QTY_ONHAND qty_onhand
from INVENTORY ip, COUNT cr where
cr.LOCATION_NO(+) = ip.LOCATION_NO and
cr.part_no(+) = ip.part_no[/tt]