Re: "UNION ALL" is failing

Thanks for the suggestions,

combining your and Guillaume Lelarge suggestions
I was able to get it two work.

I had to do two things.

1. take away the with's and just drop them into my from statement
2. remove the order by's

so the working sql is as follows:

select
'Phone 611 IVR',
'New States',
b.node,
a.accesses as old,
b.accesses as new
from
(
select
channel,
node,
accesses
from
storage
where monthly = '11-06'
) as a right join (
select
channel,
node,
accesses
from
storage
where monthly = '11-07'
) as b on a.node=b.node
where
a.accesses is null
and
b.channel = '611 IVR'

union all

select
'Web OLAM',
'New States',
b.node,
a.accesses as old,
b.accesses as new
from
(
select
channel,
node,
accesses
from
storage
where monthly = '11-06'
) as a right join (
select
channel,
node,
accesses
from
storage
where monthly = '11-07'
) as b on a.node=b.node
where
a.accesses is null
and
b.channel = 'olam'

Thanks again for the help.

On Fri, Aug 26, 2011 at 9:52 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Joy Smith <freestuffanddeals(at)gmail(dot)com> writes:
> > column types are the same so I don't know why this 'union all' is
> failing.
>
> It's a syntax error --- got nothing to do with column types.
>
> I think what you need to do is parenthesize the first subquery. ORDER
> BY isn't allowed to be attached to a UNION subquery otherwise. You're
> probably going to need to parenthesize the second subquery too ---
> otherwise it will think that that ORDER BY applies to the UNION result,
> not the subquery.
>
> I don't offhand remember the syntactic precedence of WITH versus UNION,
> but trying to attach WITHs to the subqueries might be another reason to
> need parentheses.
>
> regards, tom lane
>