Saira : how do u want to achive this ? using procedure or a single update stmt ?
-----Original Message----- Somani Sent: Tuesday, February 25, 2003 3:27 PM To: Multiple recipients of list ORACLE-L 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: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] 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).
