P.S. Yay, it works now - thank you everyone, I'm really grateful for your advise. :)
[email protected] kirjutas pühapäev, 23. mai 2021 kl 11:15:47 UTC+3: > 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/d0b88c9b-6481-4c0e-b1a6-084b2ff680c4n%40googlegroups.com.
