AW: Mean SQL statement!

From: Stefan Jahnke <Stefan.Jahnke_at_bov.de>
Date: Tue, 2 Mar 2004 17:48:19 +0100
Message-ID: <87F172BCF111D0489340AB3CB16A011E0DD23B@EBMS20.bov.int>

Could you post the structure of the tables your using?

Thanks,
Stefan

-----Ursprüngliche Nachricht-----

Von: Saira Somani-Mendelin [mailto:saira_somani_at_yahoo.com] Gesendet: Tuesday, March 02, 2004 4:57 PM An: oracle-l_at_freelists.org
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_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

Please see the official ORACLE-L FAQ: http://www.orafaq.com

Received on Tue Mar 02 2004 - 10:45:30 CST