This worked:
SELECT A.WHSE_CODE,A.ITEM_NUM,B.LAST_COST FROM ITEM_W A,(SELECT DISTINCT
(LAST_COST),SUBSTR(ITEM_NUM,1,6) ITEM_NUM FROM ITEM_W WHERE
LAST_COST<>0) B WHERE SUBSTR(A.ITEM_NUM,1,6) = B.ITEM_NUM AND
RTRIM(A.WHSE_CODE) NOT LIKE ('CD%');
A suggestion from someone on the COGNOS mailing list. I created a view
in Oracle and now I can easily access it from a report.
Here are some additional thoughts:
Thanks to all who helped but I have to say, just because most of you
have been in the business for over a decade (or even half a decade) does
not mean that all of us have, so when we do ask a question, it is
usually because WE DON'T KNOW or CAN'T FIND THE ANSWER or some such dire
constraint. Kindly do not assume that we have not done our homework and
that we want you to do it for us. Give me maybe half a decade and I'll
be able to answer my own questions and some of yours.
There is such a thing as too much information and sometimes wading
through it takes a lot of time when you are on a deadline and have
people breathing down your back. I thought the list was for everyone
requiring some assistance or to exchange ideas. I'm sure I'll be
receiving hate mails from some of you out there ;) I already received
snarly remarks when I posted the first message.
Thanks again and I do learn something new from this list every day.
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).
--
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).
