Hi Helen!

I think that Marcins problem is that in-line subselects and group by doesn't work nicely together in Firebird (at least Firebird 1.5 and 2.1 (I think, I didn't check properly when testing at home yesterday, so it may be 2.0) seemed to behave sub-optimally when I tried a bit yesterday).

SELECT a.a, (select rdb$db_key from rdb$database b), count(*)
from a
group by a.a, 2

works OK, whereas

SELECT a.a, (select rdb$db_key from rdb$database b where a.b = b.rdb$relation_id), count(*)
from a
group by a.a, 2

fails to prepare. It is possible to get the above statement to prepare by changing to

SELECT a.a, (select rdb$db_key from rdb$database b where a.b = b.rdb$relation_id), count(*)
from a
group by a.a, 2, a.b

but this is likely to return more rows than what Marcin wants (in his case it would return one row for every KTOW.ID). That's why I suggested using the WITH statement yesterday (although it was already too late for me to bother to modify the statement so that I could try it on my computer - and some things just work better when untested ;o)).

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Helen Borrie
Sent: 19. november 2008 01:05
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Group by question/problem

At 08:38 PM 18/11/2008, you wrote:

>Hello All,
>
>I have following query:
>
>SELECT KD.DATA_DOST
> ,T.KOD
> ,(SELECT 1 FROM RDB$DATABASE WHERE
> EXISTS(SELECT KR.ODBIORCA_ID FROM MG_KONTRAKT_REZ KR WHERE
>KR.KONTENER_TOW_ID = KTOW.ID AND KR.ODBIORCA_ID = 'ODB')) REZ_CM
> ,COUNT (KTOW.KONTENER_ID)
>
>FROM MG_KONTRAKT_KONTENERY_TOW KTOW
>INNER JOIN MG_KONTRAKT_KONTENERY KT ON (KTOW.KONTENER_ID =
>KT.KONTENER_ID AND KT.STATUS >= 0)
>LEFT JOIN MG_INDEKS T ON (T.ID = KTOW.TOWAR_ID)
>LEFT JOIN MG_KONTRAKT_DOST KD ON (KTOW.DOST_ID = KD.DOST_ID)
>GROUP BY 1,2,3
>
>What should be the proper 'group by' part to get this query working - is
>it possible at all?

SELECT
KD.DATA_DOST,
T.KOD,

/* Option 1 has potential multiple rows from singleton select error */
(SELECT 1 FROM MG_KONTRAKT_REZ KR
WHERE KR.KONTENER_TOW_ID = KTOW.ID
AND KR.ODBIORCA_ID = 'ODB') REZ_CM,

/* Option 2 needs a non-null for no-match condition*/
CASE WHEN
(EXISTS (SELECT 1 FROM MG_KONTRAKT_REZ KR
WHERE KR.KONTENER_TOW_ID = KTOW.ID
AND KR.ODBIORCA_ID = 'ODB')
) THEN '1' ELSE '' END REZ_CM,


COUNT (KTOW.KONTENER_ID)

FROM MG_KONTRAKT_KONTENERY_TOW KTOW

INNER JOIN MG_KONTRAKT_KONTENERY KT
ON (KTOW.KONTENER_ID = KT.KONTENER_ID
AND KT.STATUS >= 0)

LEFT JOIN MG_INDEKS T
ON (T.ID = KTOW.TOWAR_ID)

LEFT JOIN MG_KONTRAKT_DOST KD
ON (KTOW.DOST_ID = KD.DOST_ID)

GROUP BY 1,2,3

I don't have anything to test this with but I wouldn't discard the possibility that the potential nulls for KD.DATA_DOST and T.KOD (due to the outer joins) could obstruct the grouping (and raise an exception) or produce odd results.

./heLen