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 GMicrosoft
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_cor p_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

Garfield wrote:

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@x.washington.edu
(replace 'x' with 'u' to respond)

Would you mind telling us what is not working?

Rene

--
Rene Nyffenegger
http://www.adp-gmbh.ch/

As Rene said I cannot tell squat from your post. From your request to
name a subquery I would guess what you really want is the SELECT WITH
feature which is also called sub-query factoring. The general form is
below:

With
avg_dept_sal as (select deptno, avg(sal) as deptavg
from emp
group by deptno)
,avg_sal as (select avg(sal) as coavg from emp)
select empno, ename, job, deptno, sal
from emp e
where sal > (select deptavg from avg_dept_sal b
where b.deptno = e.deptno)
and sal > (select coavg from avg_sal)
order by deptno, ename, job;

This isn't a great expample as the WITH clause is most useful when the
same subquery needs to appear several times in the query.

The other probable feature you are asking for is how to place a label
on an inline view:

select a.col1, a.col2, b.col3
from table_a a,
(select d.col3 from table_d d where .... ) b <== label so
d.col3 is now b.col3 to outer query
where a.col1 = b.col1
....


HTH -- Mark D Powell --

Garfield wrote:

I'll take a stab at this...

================================================== ==============================
THE CONTEXT

1. Assuming we are looking to join three tables:

1A. Table 1: Naughty_List (Person_ID, xmas_year, Name, Reason, Create_date)
1B. Table 2: People (Person_ID, Name, birth_date)
1C. Table 3: Good_Deeds (Person_ID, deed_date, description)

2. Santa's elves categorize presents into several categories:
0=Coal
1=Small Present
2=Big Present
3=Gigantic Present
4=Trip to Disney World

3. If a child is on the naughty list, they get Coal (category=0).

4. For every 10 good deeds a child performs, they are "bumped" up a
category, with the base category being Small Present (category=1).

5. Santa's elves have a little problem with the 2004 xmas season: They
need to make sure that children (age < 18) who have been really good (>
10 good deeds during the year) get a present regardless of being on the
naughty list. However, if they are on the naughty list and _do_ get a
present, it will only be a "Small Present"

Given these requirements to add context to this little query, there are
a number of ways to slice this up, but I want to create an example that
illustrates named subqueries. Given the SQL provided by Garfield, I have
striven to create an example appropriate to my perception of his skill
level.



================================================== ==============================
THE ANSWER (Partial)

SELECT People.People_ID people_id
,People.Name given_name
,naughty_list.name name_on_rap_sheet
,DEED_COUNT.Frequency good_deeds
,decode( naughty_list.people_id
,People.People_ID, 'YES'
,'NO' ) on_naughty_list
FROM naughty_list
,People
,( SELECT People_ID,
COUNT(*) Frequency
FROM Good_Deeds
WHERE to_char( deed_date, 'yyyy' ) = '2004'
GROUP BY People_ID ) DEED_COUNT
WHERE people.People_ID = naughty_list.people_id (+)
AND people.People_ID = DEED_COUNT.People_ID (+)
AND (2005 - to_number( to_char( people.birth_date
,'yyyy')) < 18)



================================================== ==============================
THE EXPLANATION

In looking at the SQL, you can see that we performed a simple
aggregation query to get the count of good deeds for the year. We gave
this sub-query the name of DEED_COUNT and we also roped it into an outer
join in order to figure out how many good deeds each child had done
during the year.

If you are really interested in seeing who gets what, you can then take
the entire SQL statement, wrap it with a couple of parentheses and give
it your own sub-query name. We can call this the "Optional exercise" for
our gifted audience to attempt. (Hint: decode (or CASE)
<result>.good_deeds).


================================================== ==============================
Best Regards and happy coding!

Dave


N. David Sanabria
Specialist, DA
The Hartford, Infrastructure Solutions Department
Online Services Information Delivery
@: david.sanabria (AT) hartfordthe (DOT) com
Don't be afraid to take a big step if one is indicated; you can't cross
a chasm in two small jumps. David Lloyd George