Re: "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