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
<guilla...@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
>
>
