Mean SQL statement!

From: Saira Somani-Mendelin <saira_somani_at_yahoo.com>
Date: Tue, 2 Mar 2004 10:56:54 -0500
Message-ID: <007401c4006e$fde01460$e20110ac@itlap01>

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_at_freelists.org 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
-----------------------------------------------------------------

Received on Tue Mar 02 2004 - 10:09:05 CST