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@sss.pgh.pa.us> wrote:
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