UNION not working... why?
Hi there,
I have two rather simple queries, which I would to UNION, but somehow
I always get an error message for the UNION ("ERROR: syntax error at
or near "UNION"")
Each query selects the country name ("Switzerland" in this case), the
most recent year in the table and its value.
What is wrong with it? Thanks for any hints!
SELECT
c.name,
d.year_start,
d.value
FROM
emissions_so2_total_rivm AS d
LEFT JOIN
countries AS c ON c.id = d.id_country
WHERE
((c.iso_2_code = 'CH') OR (c.iso_3_code = 'CH'))
ORDER BY
d.year_start DESC
LIMIT 1
UNION ALL
SELECT
c.name,
d.year_start,
d.value
FROM
pop_density AS d
LEFT JOIN
countries AS c ON c.id = d.id_country
WHERE
((c.iso_2_code = 'CH') OR (c.iso_3_code = 'CH'))
ORDER BY
d.year_start DESC
LIMIT 1
____________________________________________________________________
Stefan Schwarzer
Lean Back and Relax - Enjoy some Nature Photography
http://photoblog.la-famille-schwarzer.de
Appetite for Global Data? UNEP GEO Data Portal:
http://geodata.grid.unep.ch
____________________________________________________________________
Stefan Schwarzer wrote:
Hi there,
I have two rather simple queries, which I would to UNION, but somehow I
always get an error message for the UNION ("ERROR: syntax error at or
near "UNION"")
SELECT
ORDER BY
LIMIT 1UNION ALL
...
I think it's complaining about the order by/limit. The UNION is part of
the standard SELECT grammar and has its own ORDER BY (if you see what
I'm getting at).
Try SELECT * FROM (SELECT ... ORDER BY) AS foo UNION ...
--
Richard Huxton
Archonet Ltd
Hello
use derived tables
SELECT *
FROM (SELECT c.name, d.year_start, d.value
FROM emissions_so2_total_rivm AS d
LEFT JOIN
countries AS c ON c.id = d.id_country
WHERE ((c.iso_2_code = 'CH') OR (c.iso_3_code = 'CH'))
ORDER BY d.year_start DESC
LIMIT 1) c1
UNION ALL
SELECT *
FROM (SELECT c.name, d.year_start, d.value
FROM emissions_so2_total_rivm AS d
LEFT JOIN
countries AS c ON c.id = d.id_country
WHERE ((c.iso_2_code = 'CH') OR (c.iso_3_code = 'CH'))
ORDER BY d.year_start DESC
LIMIT 1) c2
Regards
Pavel Stehule
Show quoted text
On 13/12/2007, Stefan Schwarzer <stefan.schwarzer@grid.unep.ch> wrote:
Hi there,
I have two rather simple queries, which I would to UNION, but somehow I
always get an error message for the UNION ("ERROR: syntax error at or near
"UNION"")Each query selects the country name ("Switzerland" in this case), the most
recent year in the table and its value.What is wrong with it? Thanks for any hints!
SELECT
c.name,
d.year_start,
d.value
FROM
emissions_so2_total_rivm AS d
LEFT JOIN
countries AS c ON c.id = d.id_country
WHERE
((c.iso_2_code = 'CH') OR (c.iso_3_code = 'CH'))
ORDER BY
d.year_start DESC
LIMIT 1UNION ALL
SELECT
c.name,
d.year_start,
d.value
FROM
pop_density AS d
LEFT JOIN
countries AS c ON c.id = d.id_country
WHERE
((c.iso_2_code = 'CH') OR (c.iso_3_code = 'CH'))
ORDER BY
d.year_start DESC
LIMIT 1____________________________________________________________________
Stefan Schwarzer
Lean Back and Relax - Enjoy some Nature Photography
http://photoblog.la-famille-schwarzer.deAppetite for Global Data? UNEP GEO Data Portal:
http://geodata.grid.unep.ch____________________________________________________________________
Oh great. Thanks a lot.
But now, I have another problem in this context. If I use text in the
SELECT statement (so, that the final output gives me the name of the
selected variables, plus the year and the value) than I get this
error message: ERROR: failed to find conversion function from
"unknown" to text
If I just use one of the SELECTs, it works fine. But as soon as I use
the UNION ALL and add the second SELECT, the error message appears.
What am I doing wrong? Thanks for any hints!
Show quoted text
SELECT *
FROM (SELECT 'pop_density' AS name, d.year_start, d.value
FROM emissions_so2_total_rivm AS d
LEFT JOIN
countries AS c ON c.id = d.id_country
WHERE ((c.iso_2_code = 'CH') OR (c.iso_3_code =
'CH'))
ORDER BY d.year_start DESC
LIMIT 1) c1
UNION ALL
SELECT *
FROM (SELECT 'gdp' AS name, d.year_start, d.value
FROM emissions_so2_total_rivm AS d
LEFT JOIN
countries AS c ON c.id = d.id_country
WHERE ((c.iso_2_code = 'CH') OR (c.iso_3_code =
'CH'))
ORDER BY d.year_start DESC
LIMIT 1) c2
Stefan Schwarzer <stefan.schwarzer@grid.unep.ch> writes:
But now, I have another problem in this context. If I use text in the
SELECT statement (so, that the final output gives me the name of the
selected variables, plus the year and the value) than I get this
error message: ERROR: failed to find conversion function from
"unknown" to text
[squint...] Try casting those literals to text explicitly, ie
SELECT 'pop_density'::text AS name. There's some code in there
that makes this work for simple UNION cases, but maybe not so much
for select with a left join and order by and limit inside a union :-(
regards, tom lane
But now, I have another problem in this context. If I use text in the
SELECT statement (so, that the final output gives me the name of the
selected variables, plus the year and the value) than I get this
error message: ERROR: failed to find conversion function from
"unknown" to text[squint...] Try casting those literals to text explicitly, ie
SELECT 'pop_density'::text AS name. There's some code in there
that makes this work for simple UNION cases, but maybe not so much
for select with a left join and order by and limit inside a union :-(
You guys are really great! Yep, it works like that like a charm...
Thanks a lot for your help!