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 <t...@sss.pgh.pa.us> wrote: > Joy Smith <freestuffandde...@gmail.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 >
