"UNION ALL" is failing

On Fri, 2011-08-26 at 09:28 -0400, Joy Smith wrote:

column types are the same so I don't know why this 'union all' is failing.
Any ideas?

You cannot have an ORDER BY before the UNION ALL. The manual says:

[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
* | expression [ [ AS ] output_name ] [, ...]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
[ WINDOW window_name AS ( window_definition ) [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS
{ FIRST | LAST } ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start [ ROW | ROWS ] ]
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
[ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...]
]

See the ORDER AFTER the (one or many) UNION? you didn't follow this, so
you have a syntax error.

http://www.postgresql.org/docs/9.0/interactive/sql-select.html

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
here is the error:

ERROR: syntax error at or near "UNION"
LINE 17: UNION ALL
^

********** Error **********

ERROR: syntax error at or near "UNION"
SQL state: 42601
Character: 278

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Here is the query:

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

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
here is the table structure:

-- Table: "storage"

-- DROP TABLE "storage";

CREATE TABLE "storage"
(
node character varying,
accesses double precision,
monthly character varying,
model character varying,
channel character varying,
qualified character varying,
bigintmark bigserial NOT NULL,
insertiondate timestamp with time zone NOT NULL DEFAULT now(),
CONSTRAINT aso PRIMARY KEY (bigintmark)
)
WITH (
OIDS=FALSE
);
ALTER TABLE "storage" OWNER TO postgres;

--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com

Joy Smith <freestuffanddeals@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

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:

Hello Guillaume, thanks your and Tom's solutions worked.

I did find the page you cited though I admit when I was reading through all
the bracets i was not sure if it was telling me a precedence, order or what.
I am sure as i get better the following will read quite clearly.

[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
* | expression [ [ AS ] output_name ] [, ...]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
[ WINDOW window_name AS ( window_definition ) [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS
{ FIRST | LAST } ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start [ ROW | ROWS ] ]
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
[ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...]
]

thanks again.

On Fri, Aug 26, 2011 at 9:46 AM, Guillaume Lelarge
<guillaume@lelarge.info>wrote:

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: