Hi
Could you post the structure of the tables your using?
Thanks,
Stefan
-----Ursprüngliche Nachricht-----
Von: Saira Somani-Mendelin [mailto:saira_somani@(protected)]
Gesendet: Tuesday, March 02, 2004 4:57 PM
An: oracle-l@(protected)
Betreff: Mean SQL statement!
I need help :)
I have to write a SELECT statement which addresses the following problem
(because I eventually have to turn this into a view which can be
accessed through the application's presentation layer):
Each item does not necessarily have a PRICE record associated with it.
In the case where it doesn't have a PRICE record, it has to get the $$$
from another table called ITEM_W. How do I get this into the following
SELECT statement? Not only that! I then have to use this $$$ value and
calculate a unit $$$ using a multiplier from another table, and then do
some other calculation using the unit $$$ and another multiplier and
another number to get an extended $$$ value.
Here's the statement as it is:
SELECT
D.CUST_NUM,
F.CUST_GROUP,
D.SHIP_NUM,
A.ITEM_NUM,
(SELECT Z.REQUEST_ITEM
FROM
ITEM_C Z
WHERE D.CUST_NUM = Z.CUST_NUM AND
D.ITEM_NUM = Z.ITEM_NUM) AS REQUESTED_ITEM,
A.REPORT_ITEM_NUM,
D.SELL_UOM,
C.UOM,
C.MULTIPLIER AS SELL_UOM_MULT,
(SELECT Z.PRICE_1
FROM
PRICE Z
WHERE
A.REPORT_ITEM_NUM = Z.ITEM_NUM AND
F.CUST_GROUP = Z.CUST_GROUP AND
E.CUST_NUM = F.CUST_NUM) AS PRICE
FROM
ITEM A,
ITEM_SELL_UOM B,
UOM C,
CUST_SHP_ITEM D,
CUST_SHP E,
CUSTOMER F
WHERE
A.ITEM_NUM = B.ITEM_NUM AND
B.UOM_ID = C.UOM_ID AND
A.ITEM_NUM = D.ITEM_NUM AND
D.SELL_UOM = C.UOM AND
D.SHIP_NUM = E.SHIP_NUM AND
D.CUST_NUM = E.CUST_NUM AND
E.CUST_NUM = F.CUST_NUM AND
E.CUST_NUM = '33' AND
E.SHIP_NUM = '14'
And here is some of the output:
CUST_NUM CUST_GROUP SHIP_NUM ITEM_NUM
REQUESTED_ITEM REPORT_ITEM_NUM SELL_UOM UOM SELL_UOM_MULT
PRICE
33 SWHS 14 114012 66600 114012 EA EA 1
51.49
33 SWHS 14 114013 66610 114013 EA EA 1
45.55
33 SWHS 14 114017 66620 114017 EA EA 1
120.93
33 SWHS 14 12720 12720 12720 EA EA 1
33 SWHS 14 63510 63510 63510 EA EA 1
33 SWHS 14 115910 66980 115910 EA EA 0.02
57.94
33 SWHS 14 21901 66990 21901 PK50 PK50 1
62.09
Thanks in advance!!!
Saira
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
