Обсуждение: UNION not working... why?

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!