Oracle-L: RE: SQL struggle

From: Saira Somani <saira_somani_at_yahoo.com>
Date: Wed, 26 Feb 2003 04:33:51 -0800
Message-ID: <F001.00559D3C.20030226043351@fatcity.com>

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_at_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_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: Saira Somani
INET: saira_somani_at_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_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).

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: <netmadcap_at_netzero.com
INET: netmadcap_at_netzero.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

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Saira Somani
INET: saira_somani_at_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

RE: SQL struggle - UPDATE too?