Re: How to name a subquery?

Garfield wrote:

> 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
>

1. Why do you "know" it is possible?
2. If it is possible ... so what?

What you want may be possible.

But my interpretation of what you have asked leads me to say it is not and were it ...it would be needless/meaningless. Perhaps you can better explain to someone else.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)