Greetings. On Sat, 2021-05-22 at 21:26 -0700, Evgenij Ryazanov wrote: > It may be surprising, but validity of some queries depends on the > data. > H2 and some other DBMS support optional feature T301, “Functional > dependencies” from the SQL Standard.
Indeed! > SELECT A, B, COUNT(C) FROM TEST GROUP BY A is not valid for DBMS > without the mentioned feature @OP: You can rewrite your query like below in order to make it work in general (without depending on T301). You can also use that form to identify any duplicates easily (using a HAVING COUNT(*)>1), which would break you original SQL Statement. Good luck. WITH t AS ( SELECT DISTINCT iwbatches.id , iwbatches.name , iwbatches.company_id , iwbatches.opener_id o_id , iwbatches.opened_when , Coalesce( Concat( openers.firstname, ' ', openers.lastname ), '' ) o_by , Coalesce( To_Char( iwbatches.opened_when, 'YYYY-MM-DD hh24:mi' ), '' ) o_when , companies.name company_name , companies.code company_code , companies.streetaddress company_streetaddress , companies.settlement company_settlement , companies.regcode company_regcode , companies.vatcode company_vatcode , companies.contact company_contact , companies.email company_email , companies.phone company_phone , iwbatchrows.id rows_id FROM iwbatches LEFT JOIN iwbatchrows ON iwbatches.id = iwbatchrows.iwbatch_id AND iwbatchrows.needed IS NOT NULL LEFT JOIN persons openers ON iwbatches.opener_id = openers.id LEFT JOIN companies ON iwbatches.company_id = companies.id WHERE iwbatches.finished_when IS NULL AND ( iwbatches.opened_when >= Dateadd( 'DAY', ( SELECT CAST(value AS INT) FROM cfg WHERE key = 'iwbatches.oldest.days' ), CURRENT_TIMESTAMP ) ) AND ( iwbatches.opened_when <= Dateadd( 'DAY', ( SELECT CAST(value AS INT) FROM cfg WHERE key = 'iwbatches.youngest.days' ), CURRENT_TIMESTAMP ) ) ) , aggregate AS ( SELECT id , Count( rows_id ) rows FROM t GROUP BY id ) SELECT aggregate.id , aggregate.rows , t.id , t.name , t.company_id , t.o_id , t.opened_when , t.o_by , t.o_when , t.company_name , t.company_code , t.company_streetaddress , t.company_settlement , t.company_regcode , t.company_vatcode , t.company_contact , t.company_email , t.company_phone FROM aggregate LEFT JOIN t ON aggregate.id = t.id ORDER BY aggregate , id ; -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/e4200bf8dc55e63680b1c02f181e938dc5a8542a.camel%40manticore-projects.com.