Column "..." does not exist (view + union)
От | Stefan Weiss |
---|---|
Тема | Column "..." does not exist (view + union) |
Дата | |
Msg-id | 4EEC0533.9050007@gmail.com обсуждение исходный текст |
Ответы |
Re: Column "..." does not exist (view + union)
|
Список | pgsql-sql |
Assuming the following simple setup with two data tables, one mapping table, and one view - -- ---------------------------------------------------------------- CREATE TABLE dossier ( id SERIAL NOT NULL PRIMARY KEY ); CREATE TABLE contact ( id SERIAL NOT NULL PRIMARY KEY, name TEXT NOT NULL, firstname TEXT NULL ); CREATE TABLE dossier_contact ( dossier_id INTEGER NOT NULL REFERENCES dossier(id), contact_id INTEGER NOT NULL REFERENCEScontact(id), ctype INTEGER NOT NULL, PRIMARY KEY (dossier_id, contact_id) ); CREATE VIEW dossier_contact_v AS SELECT dc.dossier_id, dc.contact_id, dc.ctype, (CASE WHENc.firstname IS NOT NULL THEN c.name || ', ' || c.firstname ELSE c.name END) AS name FROM dossier_contact dc JOIN contact c ON c.id = dc.contact_id; -- ---------------------------------------------------------------- - running this query - SELECT name FROM dossier_contact_v WHERE dossier_id = 56993 AND ctype = 234 UNION SELECT name FROM dossier_contact_v WHERE dossier_id = -1 AND ctype = -1 ORDER BY ctype; - fails with the following error message: ERROR: column "ctype" does not exist LINE 10: ORDER BY ctype; ^ The same query works fine without the ORDER BY, without the UNION, or when I select the "ctype" column in addition to "name". Why? Using an alias in the FROM clause gives a different error: SELECT x.name FROM dossier_contact_v x WHERE x.dossier_id = 56993 AND x.ctype = 234 UNION SELECT x.name FROM dossier_contact_v x WHERE x.dossier_id = -1 AND x.ctype = -1 ORDER BY x.ctype; ERROR: missing FROM-clause entry for table "x" LINE 10: ORDER BY x.ctype ^ I am using "PostgreSQL 8.4.10 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.5.real (Ubuntu/Linaro 4.5.2-8ubuntu4) 4.5.2, 64-bit". thanks, stefan
В списке pgsql-sql по дате отправления: