How to name a subquery?

From: Garfield <pei.peizhang_at_gmail.com>
Date: 22 Apr 2005 14:35:15 -0700
Message-ID: <1114205715.810211.117220@l41g2000cwc.googlegroups.com>

I need to build a large query that uses the subquery I created. I know there is a way to name the subquery so that I could pull the name of the subquery and the constances in it. It's just nor working properly at the moment. Would you mind take a look and let me know?

select topcust.cust_master_corp_num,

		topcust.cust_master_corp_name,
		rprod.PRODUCT_CLASS_CODE,
		rprod.PARENT_PRODUCT_CLASS_DESC,
		rprod.CEI_PRODUCT_NUM,
		rprod.PRIMARY_PRODUCT_CROSS_REF as SKU,
		rprod.PRODUCT_DESCRIPTION as Description,
		rprod.BASE_UOM_CODE,
		rprod.MIN_SELL_UOM_CODE,
		rprod.MINSELL_CONVERT_TO_BASE_FACTOR,
		sum(msales.NET_QTY_IN_BASE_UOM) as units,
		sum(msales.NET_ACTUAL_COST) as cost,
		sum(msales.NET_SALES_AMOUNT) as sales,
		sum(msales.GROSS_MARGIN_AT_ACTUAL_COST) as GM$
from   	ref_cei_std_product_base_uom rprod inner join
monthly_rgstrd_sales_by_prod msales
		on msales.CEI_PRODUCT_NUM=rprod.CEI_PRODUCT_NUM
		inner join
		ref_customer_unit rcust
		on rcust.CUST_UNIT_NUM=msales.CUST_RECEIVING_UNIT_NUM
		inner join

		(select
     CUST_MASTER_CORP_NUM

from (
     select
          rcust.CUST_MASTER_CORP_NUM,
          rcust.CUST_MASTER_CORP_NAME,
          SUM(msales.NET_SALES_AMOUNT) as Sales
     from
          ref_cei_std_product_base_uom rprod
          INNER JOIN monthly_rgstrd_sales_by_prod msales
               ON RPROD.CEI_PRODUCT_NUM=MSALES.CEI_PRODUCT_NUM
          INNER JOIN ref_customer_unit rcust
               ON MSALES.CUST_RECEIVING_UNIT_NUM=RCUST.CUST_UNIT_NUM
     where
          rprod.PARENT_PRODUCT_CLASS_CODE ='03'
          AND RPROD.PRODUCT_HIERARCHY_CODE='CLS'
          AND RPROD.PRODUCT_TYPE_CODE IN ('PROD','SRVC','CHRG')
          AND rprod.PRODUCT_CLASS_CODE in

('1080','1090','1070','1380','1410','1500','1540','1740')

          AND
MSALES.CALENDAR_MONTH_END_DATE=TO_DATE('03/31/2005','MM/DD/YYYY')

          AND rcust.Account_type_code <> 'INTR'
     group by
          rcust.CUST_MASTER_CORP_NUM,
          rcust.CUST_MASTER_CORP_NAME
     ORDER BY
          NVL(SALES,0) DESC /* you need NVL if SALES is ever NULL, so
it gets sorted properly */
     )
where
     ROWNUM <= 50)  TopCust
	 on rcust.CUST_MASTER_CORP_NUM=topcust.cust_master_corp_num
where	rprod.PRODUCT_HIERARCHY_CODE='CLS'
		AND RPROD.PRODUCT_TYPE_CODE IN ('PROD','SRVC','CHRG')
		AND MSALES.CALENDAR_MONTH_END_DATE=TO_DATE('03/31/2005','MM/DD/YYYY')
		AND RCUST.ACCOUNT_TYPE_CODE<>'INTR'
group by
		rprod.PRODUCT_CLASS_CODE,
		rprod.PARENT_PRODUCT_CLASS_DESC,
		rprod.CEI_PRODUCT_NUM,
		rprod.PRIMARY_PRODUCT_CROSS_REF ,
		rprod.PRODUCT_DESCRIPTION ,
		rprod.BASE_UOM_CODE,
		rprod.MIN_SELL_UOM_CODE,
		rprod.MINSELL_CONVERT_TO_BASE_FACTOR

Received on Fri Apr 22 2005 - 16:35:15 CDT