Oracle-L: Re: SQL struggle

From: Chip <ocp-dba_at_earthlink.net>
Date: Tue, 25 Feb 2003 21:29:06 -0800
Message-ID: <F001.005599B0.20030225212906@fatcity.com>

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: 'ORACLE-L_at_fatcity.com'
>
>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: ocp-dba_at_earthlink.net

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: ListGuru_at_fatcity.com (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).

Received on Tue Feb 25 2003 - 23:29:06 CST

Re: SQL struggle - UPDATE too?