AW: Mean SQL statement!

  • From: Stefan Jahnke <Stefan.Jahnke@xxxxxx>
  • To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 2 Mar 2004 17:48:19 +0100

Hi

Could you post the structure of the tables your using?

Thanks,
Stefan

-----Ursprüngliche Nachricht-----
Von: Saira Somani-Mendelin [mailto:saira_somani@xxxxxxxxx]
Gesendet: Tuesday, March 02, 2004 4:57 PM
An: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts:

  • » AW: Mean SQL statement!