Re: sql with within a with

["Jim Essinger" <dilbernator@xxxxxxxxx>]

To use your example, modified;
With

item_received as
( SELECT DISTINCT PRICDE, PRSIZE FROM pmdbfa.pmppr WHERE PRHREC >=  39294
   ),

results as
( 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 ......


The first CTE creates the table Item_Received.  The 2nd CDT uses that table
and others to create table Results.  The query uses Results and
another_table for the final result.

HTH

Jim
On Tue, Mar 25, 2008 at 4:55 PM, Hatzenbeler, Tim <
thatzenbeler@xxxxxxxxxxxxx> wrote:

> 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 ......
>
>
>
> ------------------------------------------------------------------------------
> This e-mail message, including any attachments, is for the
> sole use of the intended recipient(s) and may contain
> confidential or privileged information.  Any unauthorized
> review, use, disclosure or distribution is prohibited. If
> you are not the intended recipient, please contact the
> sender by reply e-mail and destroy the message.