I have an Oracle table called ITEM_

[saira78]

I have an Oracle table called ITEM_W that looks something like this (there are more fields but they are irrelevant for this question):

WHSE_CODE ITEM_NUM LAST_COST
------------ ------------------------------ ----------
HL1 111230 1.12
CPD-TWH 111230-OR 0
CPD-TGH 111230-OR 0
HL1 50034 .91
MSH-CDS 50034 0
CPD-TGH 50034-OR 0
HL1 650300 4.789
TWH-STAT 650300 0
CPD-TWH 650300-OR 0
CPD-TGH 650300-OR 0

I need to report on last_cost for all items that have $0 cost. Each item that has a $0 cost has an HL1 record which does have a corresponding last_cost.

Question, I can't do anything to the Oracle table, how do I get last_cost for these $0 items?

Note, some of these $0 items have a suffix of -OR and some don't but they always have a whse_code something other than 'HL1'.

If I could somehow (in the report) parse -OR out of the item_num just for the purpose of matching it with a corresponding HL1 record, I'd like to then take that last_cost value and insert it beside the original -OR item or another $0 last_cost item with the same item_num but different whse_code.

Would I have to change the catalog? How? Do I create an alias and join on something?

Does this make sense? Please help. I've tried everything.

Thanks in advance.

Saira Somani
IT Support/Analyst
Hospital Logistics Inc.

 

[carp]

SELECT item_num, last_cost
FROM item_w
WHERE whse_code = 'HL1'
AND item_num IN (SELECT item_num
FROM item_w
WHERE item_cost = 0);

or

SELECT item_num, last_cost
FROM item_w i1
WHERE whse_code = 'HL1'
AND exists (SELECT 'x'
FROM item_w i2
WHERE i2.item_cost = 0
AND i1.item_num = i2.item_num);

I haven't tested either of these, but I think they should both do what you want.