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

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