Re: "UNION ALL" is failing

[Joy Smith <freestuffanddeals@xxxxxxxxx>]

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@xxxxxxxxxxxx> wrote:

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