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/e4200bf8dc55e63680b1c02f181e938dc5a8542a.camel%40manticore-projects.com.
