Wow, thanks for educating me about new SQL features. :) I suspected this was data-dependent, but couldn't figure out how this was possible, or what exactly was data-dependent.
P.S. Also, many thanks for the new query you proposed. :) It didn't run on first try, but I'll adjust it and get things working soon. :) [email protected] kirjutas pühapäev, 23. mai 2021 kl 07:59:26 UTC+3: > 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/1a0cbffb-d1ac-4ea5-b158-4ed9dd45881fn%40googlegroups.com.
