Thanks. But neither statements work. hl1_cost is never populated for any
of the -OR items.
whse_code and item_num are the primary keys.
Thanks again for your help.
Saira
-----Original Message-----
Sent: February 26, 2003 12:29 AM
To: Multiple recipients of list ORACLE-L
Do these SQL statements work ?
SELECT whse_code
, item_num
, last_cost
, ( SELECT last_cost
FROM item_w hl1
WHERE whse_code = 'HL1'
AND REPLACE(u.item_num,'-OR') = hl1.item_num
) hl1_cost
FROM item_w u
/
UPDATE (
SELECT whse_code
, item_num
, last_cost
, ( SELECT last_cost
FROM item_w hl1
WHERE whse_code = 'HL1'
AND REPLACE(u.item_num,'-OR') = hl1.item_num
) hl1_cost
FROM item_w u
WHERE whse_code <> 'HL1'
)
SET last_cost = hl1_cost
/
Note: whse_code and item_num could be removed from the UPDATE statement.
Also, added WHERE whse_code <> 'HL1' so the source last_cost is not
updated
to its current value (reduces redo log entries and rollback segment
usage).
Have Fun :)
Saira Somani wrote:
>I am very confused (and fairly new to SQL which would be my excuse to
>post such amateurish questions on this list).
>
>Now I've been asked to update LAST_COST on item_w so it looks like
this:
>
>WHSE_CODE ITEM_NUM LAST_COST
>------------ ------------------------------ ----------
>HL1 111230 1.12
>CPD-TWH 111230-OR 1.12
>CPD-TGH 111230-OR 1.12
>HL1 50034 .91
>MSH-CDS 50034 .91
>CPD-TGH 50034-OR .91
>HL1 650300 4.789
>TWH-STAT 650300 4.789
>CPD-TWH 650300-OR 4.789
>CPD-TGH 650300-OR 4.789
>
>
>-----Original Message-----
>Sent: February 25, 2003 12:55 PM
>To: '[EMAIL PROTECTED]'
>
>List Gurus,
>
>I need help and I won't be ashamed to ask :)
>
>Oracle 8.1.7 on AIX 4.3
>
>Here is what my data looks like in a table called item_w:
>
>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
>
>If you'll notice, only the items with WHSE_CODE='HL1' have a cost
>associated with them.
>
>What I need to is:
>
>Parse ITEM_NUM for those items which have a suffix of -OR in order to
>compare with an ITEM_NUM without -OR so that I can take the last cost
>from there and display it beside the one that has -OR. Also note, there
>are some $0 cost items that don't have a suffix of -OR; I would need to
>match those up with a cost as well.
>
>So in the end, I suppose, this is the result I'm looking for:
>
>WHSE_CODE ITEM_NUM LAST_COST LAST_COST_REV
>------------ ------------- ------------- -------------
>HL1 111230 1.12 1.12
>CPD-TWH 111230-OR 0 1.12
>CPD-TGH 111230-OR 0 1.12
>HL1 50034 0.91 0.91
>MSH-CDS 50034 0 0.91
>CPD-TGH 50034-OR 0 0.91
>HL1 650300 4.789 4.789
>TWH-STAT 650300 0 4.789
>CPD-TWH 650300-OR 0 4.789
>CPD-TGH 650300-OR 0 4.789
>
>And if any of you out there use Cognos Impromptu, perhaps you could
tell
>me how I can achieve these results in a report.
>
>Thanks in advance for your time,
>
>
>Saira Somani
>IT Support/Analyst
>Hospital Logistics Inc.
>
>
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Chip
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Saira Somani
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
