Hi again, Marcin!

If you don't have duplicate entries for KR.KONTENER_TOW_ID with 'ODB' in
ODBIORCA_ID, then the solution you found is good - but if there are
duplicates, you may get a higher count than what you want.

The reason that my original suggestion didn't work and never returned
null, was actually a flaw in Firebird when selecting constants with WITH
and then using LEFT JOIN to that CTE. I asked on firebird-devel and
Adriano answered that it is fixed in Firebird 2.5 Beta.

Set

Marcin Bury wrote:

> Helen, Svein, Paul...
>
> I have tested all your suggestions and here are some comments about results:
>
> Svein's idea with CTE' returns '1' in REZ_CM for every record, I have
> tried to figure it out why, but this kind of select statement is a bit
> complicated for me right now ...
>
> Have tried Helen option 2, but the query failed to prepare - same error
> -104 saying:
> Invalid expression in the select list (not contained in either an
> aggregate function or the GROUP BY clause).
>
> I don't know if Paul suggestions including KR.ODBIORCA_ID column in
> select did show up on the list (it could be direct email), but I got
> following query working:
>
> SELECT KD.DATA_DOST
> ,T.KOD
> ,KR.ODBIORCA_ID 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_KONTRAKT_REZ KR on (KR.KONTENER_TOW_ID = KTOW.ID AND
> KR.ODBIORCA_ID = 'ODB')
> 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
>
> Since REZ_CM in very first statement should only indicate records
> meeting criteria KR.ODBIORCA_ID = 'ODB' and have value 1 or null, it can
> hold value 'ODB' or null as well, since in my application I will check
> if REZ_CM has ANY value or not.
>
> Thank you all for you help.
>
> Helen, does your supplement for Firebird book (already on the bookshelf
> :-) cover CTE and other new fancy features for version 2.1?
>
> I must admitt that I was looking forward to see Svein replays - he is
> known that he likes such 'select statement' puzzles...
>
> Thanks once again
>
> Marcin