Reporting question

[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.

 

[drlex]

If the table is not too numerous, can you create a field that is just the numeric part item_num (using substring and either locate or position to truncate the item_num and remove the "-OR" if present) and then use running-maximum of last_cost to populate a non-zero cost field?

Otherwise, as you note, it should be possible to do an alias and then a join on a Item_W:truncated item_num = Alias Item_W:item_num and Alias Item_W: whse_code="HL1" (the truncation to be in a similar fashion as above) so that you can return the last_cost from the Alias Item_W table
HTH
lex