"UNION ALL" is failing

From: Joy Smith <freestuffanddeals(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: "UNION ALL" is failing
Date: 2011-08-26 13:28:58
Message-ID: CAJqhhiPsxUroD23ea1iADkTovmsoOOMrsMGBKgh-nwAYsXd3bQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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;

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Guillaume Lelarge 2011-08-26 13:46:12 Re: "UNION ALL" is failing
Previous Message Devrim GÜNDÜZ 2011-08-26 13:17:19 Re: Postgresql 9.0.4 SRPMS