The whole point of "with" is to factor out redundancy, and yet here you are
going and repeating the 2 "with" declarations; also the declarations have the
same names, which would be a problem, besides being redundant.
Try it like this instead:
with ...
(select ...)
union all
(select ...)
order by node
But moreover, I think you can avoid the union and doubled main selects, by
saying this instead:
with a as
(
select channel,node,accesses from storage where monthly = '11-06'
),
b as
(
select channel,node,accesses from storage where monthly = '11-07'
)
select
b.node
from a right join b on a.node=b.node
where a.accesses is null and b.channel in ('611 IVR', 'olam')
order by node
-- Darren Duncan
Joy Smith wrote:
> with a as
> (
> select channel,node,accesses from storage where monthly = '11-06'
> ),
> b as
> (
> select channel,node,accesses from storage where monthly = '11-07'
> )
>
> select
> b.node
> from a right join b on a.node=b.node
> where a.accesses is null and b.channel = ('611 IVR')
> order by node
>
>
> UNION ALL
>
>
> with a as
> (
> select channel,node,accesses from storage where monthly = '11-06'
> ),
> b as
> (
> select channel,node,accesses from storage where monthly = '11-07'
> )
>
>
> select
> b.node
> from a right join b on a.node=b.node
> where a.accesses is null and b.channel = 'olam'
> order by node
