Thread: "UNION ALL" is failing

"UNION ALL" is failing

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

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

Re: "UNION ALL" is failing

From
Guillaume Lelarge
Date:
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


Re: "UNION ALL" is failing

From
Tom Lane
Date:
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

Re: "UNION ALL" is failing

From
Joy Smith
Date:
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:
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

Re: "UNION ALL" is failing

From
Joy Smith
Date:
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:
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


Re: "UNION ALL" is failing

From
Darren Duncan
Date:
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