Return-Path: Received: from ensim.rackshack.net (root@localhost) by orafaq.net (8.11.6/8.11.6) with ESMTP id h1QIKDb19219 for ; Wed, 26 Feb 2003 12:20:13 -0600 X-ClientAddr: 209.68.248.164 Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by ensim.rackshack.net (8.11.6/8.11.6) with ESMTP id h1QIKCh19214 for ; Wed, 26 Feb 2003 12:20:12 -0600 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id HAA50769; Wed, 26 Feb 2003 07:00:11 -0800 (PST) Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 00559E0C; Wed, 26 Feb 2003 05:28:56 -0800 Message-ID: Date: Wed, 26 Feb 2003 05:28:56 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: "Odland, Brad" Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: "Odland, Brad" Subject: RE: SQL struggle - UPDATE too? Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit CHeck your yahoo mail I sent you some psuedo code you can flesh out. BRad -----Original Message----- Sent: Wednesday, February 26, 2003 6:34 AM To: Multiple recipients of list ORACLE-L I'd prefer a procedure as it is likely that this will have to be run every week. FYI: I'm not looking for you to write the code for me, just give me some general direction as I am new to PL/SQL and now I've been asked by management to script this. This is what happens when your IT department is comprised of 1 person. Thank you kindly for all your help. Saira -----Original Message----- netmadcap@netzero.com Sent: February 25, 2003 11:14 PM To: Multiple recipients of list ORACLE-L 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: 'ORACLE-L@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: Saira Somani INET: saira_somani@yahoo.com 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@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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: