pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedSQL ERROR subquery uses ungrouped column in PostgreSQL
2+ messages / 2 participants
[nested] [flat]
* SQL ERROR subquery uses ungrouped column in PostgreSQL
@ 2025-04-30 12:29 PALAYRET Jacques <jacques.palayret@meteo.fr>
2025-04-30 13:09 ` Re: SQL ERROR subquery uses ungrouped column in PostgreSQL David Rowley <dgrowleyml@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: PALAYRET Jacques @ 2025-04-30 12:29 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
Hello,
I have an unexpected error in my following query (of course, the query has been simplified here to request help):
SELECT num_poste, to_char(dat, 'YYYYMM')::integer dat, CASE FLOOR((TO_NUMBER(TO_CHAR(dat,'DD'),'99')-1)/10) WHEN 0 THEN 1 WHEN 1 THEN 2 ELSE 3 END::smallint AS num_decade
, CASE
WHEN ( CASE FLOOR((TO_NUMBER(TO_CHAR(dat,'DD'),'99')-1)/10) WHEN 0 THEN 1 WHEN 1 THEN 2 ELSE 3 END::smallint IN (1 , 2) AND count(glot)=10 )
THEN
(
SELECT valeur
FROM table3
WHERE num_serie_coef=CASE WHEN num_poste <96000000 THEN 0 WHEN num_poste BETWEEN 97100000 and 98899999 THEN num_poste/100000 END
AND coef = 'APRIME_ANGSTROM'
AND num_mois=substr( to_char(dat,'yyyymm') , 5, 2)::smallint
)
ELSE NULL
END AS toto
FROM table1 JOIN table2 USING (num_poste)
GROUP BY num_poste, to_char(dat, 'YYYYMM') , CASE FLOOR((TO_NUMBER(TO_CHAR(dat,'DD'),'99')-1)/10) WHEN 0 THEN 1 WHEN 1 THEN 2 ELSE 3 END::smallint
ORDER BY num_poste, to_char(dat, 'YYYYMM'), CASE FLOOR((TO_NUMBER(TO_CHAR(dat,'DD'),'99')-1)/10) WHEN 0 THEN 1 WHEN 1 THEN 2 ELSE 3 END::smallint
ERROR : subquery uses ungrouped column "table1.dat" from outer query
LIGNE 10 : AND num_mois=substr( to_char(dat,'yyyymm') , 5, 2)::smallint
^
=> In the subquery, the semantic analysis of the query considers the column " dat " instead of the expression " to_char(dat,'yyyymm') ", which is actually a grouped column.
Is this normal? How can I simply resolve the problem?
Sincerely.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: SQL ERROR subquery uses ungrouped column in PostgreSQL
2025-04-30 12:29 SQL ERROR subquery uses ungrouped column in PostgreSQL PALAYRET Jacques <jacques.palayret@meteo.fr>
@ 2025-04-30 13:09 ` David Rowley <dgrowleyml@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: David Rowley @ 2025-04-30 13:09 UTC (permalink / raw)
To: PALAYRET Jacques <jacques.palayret@meteo.fr>; +Cc: pgsql-general@lists.postgresql.org
On Thu, 1 May 2025 at 00:29, PALAYRET Jacques <jacques.palayret@meteo.fr> wrote:
> => In the subquery, the semantic analysis of the query considers the column " dat " instead of the expression " to_char(dat,'yyyymm') ", which is actually a grouped column.
> Is this normal? How can I simply resolve the problem?
Calculate the dat column in a subquery in the outer-level query.
Something like:
FROM (select *,to_char(dat, 'YYYYMM') as strdat from table1) table1
JOIN table2 USING (num_poste)
then use strdat in all the places you're currently using to_char(dat, 'YYYYMM')
David
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2025-04-30 13:09 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-04-30 12:29 SQL ERROR subquery uses ungrouped column in PostgreSQL PALAYRET Jacques <jacques.palayret@meteo.fr>
2025-04-30 13:09 ` Re: SQL ERROR subquery uses ungrouped column in PostgreSQL David Rowley <dgrowleyml@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox