Thread: 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
Appetite for Global Data? UNEP GEO Data Portal:
____________________________________________________________________
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 1 > > UNION 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 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 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 > > ____________________________________________________________________ > > > > > >
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! > 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!