RE: SQL struggle - UPDATE too?
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- [EMAIL PROTECTED] 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_CODEITEM_NUM LAST_COST -- -- HL1 111230 1.12 CPD-TWH 111230-OR1.12 CPD-TGH 111230-OR1.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_CODEITEM_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-OR0 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_CODEITEM_NUM LAST_COSTLAST_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')
RE: SQL struggle - UPDATE too?
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- [EMAIL PROTECTED] 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_CODEITEM_NUM LAST_COST -- -- HL1 111230 1.12 CPD-TWH 111230-OR1.12 CPD-TGH 111230-OR1.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_CODEITEM_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-OR0 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_CODEITEM_NUM LAST_COSTLAST_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
RE: SQL struggle - UPDATE too?
Thanks. But neither statements work. hl1_cost is never populated for any of the -OR items. whse_code and item_num are the primary keys. Thanks again for your help. 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_whl1 WHERE whse_code = 'HL1' ANDREPLACE(u.item_num,'-OR') = hl1.item_num ) hl1_cost FROM item_wu / UPDATE ( SELECT whse_code , item_num , last_cost , ( SELECT last_cost FROM item_whl1 WHERE whse_code = 'HL1' ANDREPLACE(u.item_num,'-OR') = hl1.item_num ) hl1_cost FROM item_wu 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_CODEITEM_NUM LAST_COST -- -- HL1 111230 1.12 CPD-TWH 111230-OR1.12 CPD-TGH 111230-OR1.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_CODEITEM_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-OR0 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_CODEITEM_NUM LAST_COST LAST_COST_REV - - - HL1 1112301.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 6503004.789 4.789 TWH-STAT 6503000 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
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_COST0) 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_whl1
WHERE whse_code = 'HL1'
ANDREPLACE(u.item_num,'-OR') = hl1.item_num
) hl1_cost
FROM item_wu
/
UPDATE (
SELECT whse_code
, item_num
, last_cost
, ( SELECT last_cost
FROM item_whl1
WHERE whse_code = 'HL1'
ANDREPLACE(u.item_num,'-OR') = hl1.item_num
) hl1_cost
FROM item_wu
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_CODEITEM_NUM LAST_COST
-- --
HL1 111230 1.12
CPD-TWH 111230-OR1.12
CPD-TGH 111230-OR1.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_CODEITEM_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-OR0
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_CODEITEM_NUM LAST_COST LAST_COST_REV
- - -
HL1 1112301.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
RE: SQL struggle - UPDATE too?
Always take what you need and leave the rest.
...and don't take any list comments personally.
-Original Message-
Sent: Wednesday, February 26, 2003 11:29 AM
To: Multiple recipients of list ORACLE-L
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_COST0) 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_whl1
WHERE whse_code = 'HL1'
ANDREPLACE(u.item_num,'-OR') = hl1.item_num
) hl1_cost
FROM item_wu
/
UPDATE (
SELECT whse_code
, item_num
, last_cost
, ( SELECT last_cost
FROM item_whl1
WHERE whse_code = 'HL1'
ANDREPLACE(u.item_num,'-OR') = hl1.item_num
) hl1_cost
FROM item_wu
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_CODEITEM_NUM LAST_COST
-- --
HL1 111230 1.12
CPD-TWH 111230-OR1.12
CPD-TGH 111230-OR1.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_CODEITEM_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-OR0
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_CODEITEM_NUM LAST_COST LAST_COST_REV
- - -
HL1 1112301.12 1.12
CPD-TWH 111230-OR 0 1.12
CPD-TGH 111230-OR
RE: SQL struggle - UPDATE too?
Quote from one of the listers:
With free advice, you get what you paid for it.
If you bite the hand that feeds you,
you may just go hungry the next time you ask for a handout.
NOBODY owes you an answer;
regardless of how dire a situation you find yourself.
HAND!
I will end this message thread here. Thanks all. I will retreat to my
humble cubicle now.
Saira
-Original Message-
Gorden-Ozgul, Patricia E
Sent: February 26, 2003 1:15 PM
To: Multiple recipients of list ORACLE-L
Always take what you need and leave the rest.
...and don't take any list comments personally.
-Original Message-
Sent: Wednesday, February 26, 2003 11:29 AM
To: Multiple recipients of list ORACLE-L
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_COST0) 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_whl1
WHERE whse_code = 'HL1'
ANDREPLACE(u.item_num,'-OR') = hl1.item_num
) hl1_cost
FROM item_wu
/
UPDATE (
SELECT whse_code
, item_num
, last_cost
, ( SELECT last_cost
FROM item_whl1
WHERE whse_code = 'HL1'
ANDREPLACE(u.item_num,'-OR') = hl1.item_num
) hl1_cost
FROM item_wu
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_CODEITEM_NUM LAST_COST
-- --
HL1 111230 1.12
CPD-TWH 111230-OR1.12
CPD-TGH 111230-OR1.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_CODEITEM_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-OR0
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
SQL struggle
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_CODEITEM_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-OR0 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_CODEITEM_NUM LAST_COSTLAST_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).
RE: SQL struggle
Title: RE: SQL struggle (see answer below) -Original Message- From: Saira Somani [mailto:[EMAIL PROTECTED]] 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-OR0 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. Would this work? select a.whse_code, a.item_num, a.last_cost, b.last_cost as last_cost_rev from item_w a, item_w b where a.last_cost = 0 and replace (a.item_num, '-OR') = b.item_num and b.last_cost 0 union select c.whse_code, c.item_num, c.last_cost, c.last_cost as last_cost_rev from item_w c where c.last_cost 0 ;
RE: SQL struggle
Title: RE: SQL struggle Thank you for your assistance it works - and I have one more question: How can I also get the SELECT to show me the original item number i.e with the -OR? Thanks, Saira -Original Message- From: Jacques Kilchoer [mailto:[EMAIL PROTECTED] Sent: February 25, 2003 1:57 PM To: '[EMAIL PROTECTED]' Cc: '[EMAIL PROTECTED]' Subject: RE: SQL struggle (see answer below) -Original Message- From: Saira Somani [mailto:[EMAIL PROTECTED]] 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. Would this work? select a.whse_code, a.item_num, a.last_cost, b.last_cost as last_cost_rev from item_w a, item_w b where a.last_cost = 0 and replace (a.item_num, '-OR') = b.item_num and b.last_cost 0 union select c.whse_code, c.item_num, c.last_cost, c.last_cost as last_cost_rev from item_w c where c.last_cost 0 ;
RE: SQL struggle - UPDATE too?
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_CODEITEM_NUM LAST_COST -- -- HL1 111230 1.12 CPD-TWH 111230-OR1.12 CPD-TGH 111230-OR1.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_CODEITEM_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-OR0 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_CODEITEM_NUM LAST_COSTLAST_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).
RE: SQL struggle - UPDATE too?
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_CODEITEM_NUM LAST_COST -- -- HL1 111230 1.12 CPD-TWH 111230-OR1.12 CPD-TGH 111230-OR1.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_CODEITEM_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-OR0 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_CODEITEM_NUM LAST_COSTLAST_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).
Re: SQL struggle - UPDATE too?
Do these SQL statements work ? SELECT whse_code , item_num , last_cost , ( SELECT last_cost FROM item_whl1 WHERE whse_code = 'HL1' ANDREPLACE(u.item_num,'-OR') = hl1.item_num ) hl1_cost FROM item_wu / UPDATE ( SELECT whse_code , item_num , last_cost , ( SELECT last_cost FROM item_whl1 WHERE whse_code = 'HL1' ANDREPLACE(u.item_num,'-OR') = hl1.item_num ) hl1_cost FROM item_wu 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_CODEITEM_NUM LAST_COST -- -- HL1 111230 1.12 CPD-TWH 111230-OR1.12 CPD-TGH 111230-OR1.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_CODEITEM_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-OR0 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_CODEITEM_NUM LAST_COSTLAST_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).
