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

Svein Erling Tysvćr pisze:

> 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
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>