RE: sql with within a with

Not sure if you're asking "can I have more than one CTE (Common Table
Expression) in my SQL statement", but if you are, the answer is yes.
For example:

WITH CTE1 AS
(SELECT * FROM T1),
CTE2 AS
(SELECT DISTINCT * FROM CTE1)
SELECT * FROM CTE2 ORDER BY 1

You can also join to another table/query as you wish, in CTEs or final
SELECT. For example:

WITH CTE1 AS
(SELECT * FROM T1),
CTE2 AS
(SELECT DISTINCT * FROM CTE1)
SELECT * FROM CTE2 INNER JOIN myTable USING(commonField)
WHERE f1 < 17

HTH, Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i5/OS and OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: sql with within a with

Is there a way to do a with within a with?

Ie:

// this works..
with item_received as
( SELECT DISTINCT PRICDE, PRSIZE FROM pmdbfa.pmppr WHERE PRHREC >= 39294
)
select distinct synonm,synref,synrf2,mndesc,plus.* from
item_received, cadbfa.synonym hcpc,cadbfa.manfilpf prod,mmcust120.pluscharge
plus where syndsc = 'HCPC' and (item_received.pricde = hcpc.synref and
item_received.prsize = hcpc.synrf2) and (item_received.pricde = prod.mnmfg#
and item_received.prsize = prod.mnsize) and (synonm = plus.proc_code)



// but I would like to use the results of this query to do another
select/join from it, without creating a view or temp table.

With results as (

with item_received as
( SELECT DISTINCT PRICDE, PRSIZE FROM pmdbfa.pmppr WHERE PRHREC >= 39294
)
select distinct synonm,synref,synrf2,mndesc,plus.* from item_received,
cadbfa.synonym hcpc,cadbfa.manfilpf prod,mmcust120.pluscharge plus where
syndsc = 'HCPC' and (item_received.pricde = hcpc.synref and
item_received.prsize = hcpc.synrf2) and (item_received.pricde = prod.mnmfg#
and item_received.prsize = prod.mnsize) and (synonm = plus.proc_code)

)

Select * from results, another_table where ......