From: | PALAYRET Jacques <jacques(dot)palayret(at)meteo(dot)fr> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | SQL ERROR subquery uses ungrouped column in PostgreSQL |
Date: | 2025-04-30 12:29:11 |
Message-ID: | 557588747.121401914.1746016151124.JavaMail.zimbra@meteo.fr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | David Rowley | 2025-04-30 13:09:05 | Re: SQL ERROR subquery uses ungrouped column in PostgreSQL |
Previous Message | Victor Yegorov | 2025-04-30 12:19:01 | Re: alter system appending to a value |