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