| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: SQL struggle - UPDATE too?
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
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
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). -- 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: 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).Received on Wed Feb 26 2003 - 10:29:04 CST
 |
 |