Ole,

I tested the statement with the correct and valid GROUP BY clause without a
view (just a normal select statement), but there is no grouping on just the
id_campagne for that id_medewerker. Maybe I'll better explain what I want. I
want a total working time -the "((max(einde) - min(aanvang))" part for each
id_campagne. Sometimes I want to list all campaigns (id_campagne), sometimes
the campaigns for just one id_medewerker, or for just one or two id_team, or
for certain id_arbeidscode values. But every time the id_campagne must show
the total working hours.

What would be a good approach?

Regards, Harry




"Ole Willy Tuv" <owtuv@online.no> schreef in bericht
news:7CE10CAB-74D4-4129-A207-B4D884B3D24A@news.elevatesoft.com...
> Harry,
>
> << When I make a view 'bt' with:
>
> SELECT *,
> CAST((max(einde) - min(aanvang)) HOUR TO MINUTE AS VARCHAR(20)) as
> BestedeTijd
> FROM registraties
> GROUP BY id_campagne
>
> and do a
> SELECT * FROM bt WHERE id_medewerker = '20020'
>
> then the result is 0 rows. Is this incorrect (and a bug) or can't I use
> views this way? >>
>
> The reason that you get an enexpected result is that the select list
> contains several non-aggregated source columns which are not included in
the
> GROUP BY clause. While EDB (like DBISAM) allows this, it's not valid SQL
and
> the result of the grouping contains ambigous/incorrect data.
>
> The correct and valid GROUP BY clause would be:
>
> GROUP BY
>   Id_Medewerker,
>   Datum,
>   Id_Campagne,
>   Id_Arbeidscode,
>   Id_Team,
>   Geaccordeerd
>
> Ole Willy Tuv
>
>

Ole,

<< The real problem IMO is that EDB (like DBISAM) doesn't enforce the SQL
specification for grouped queries, i.e. the requirement that all source
columns referenced in the select list shall either be included in the GROUP
BY clause or contained in an aggregate function. >>

It's on my list, along with divide-by-zero (EDB ignores it and returns a
NULL) and the scalar sub-query issue with more than one column.

--
Tim Young
Elevate Software
www.elevatesoft.com

Ole,

<< I'm seeing another issue (using the database Harry posted to binaries):
>>

Got it, it's an issue with the buffer manager on exclusively-opened tables
with a lot of updates on it.  It doesn't mess up the data at all, rather the
buffer manager just loses track of where it should be positioned in terms of
a read.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< It's on my list, along with divide-by-zero (EDB ignores it and returns a
NULL) and the scalar sub-query issue with more than one column. >>

Excellent! EDB is getting better and better as the engine matures.

Ole Willy Tuv

Harry,

<< I tested the statement with the correct and valid GROUP BY clause without
a view (just a normal select statement), but there is no grouping on just
the id_campagne for that id_medewerker. >>

There's a problem with grouping in the current version - see Tim's post. I
suggest that you recheck the result when the next build is out.

Ole Willy Tuv

Ole,

<< There's a problem with grouping in the current version - see Tim's post.
I suggest that you recheck the result when the next build is out. >>

AFAIK, the bug that you found does not affect the grouping of the results in
this particular case, just the filtering on them afterwards in the second
query.  However, it is *possible* that they might affect the grouping in
general due to the nature of the bug.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim, Ole,

When the bug is fixed (next build) I will check this issue and let you know.

Thanks for your input.

Regards, Harry

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> schreef in bericht
news:8A5D1BD3-B404-4E31-A3DC-0BFE3204DA06@news.elevatesoft.com...
> Ole,
>
> << There's a problem with grouping in the current version - see Tim's
post.
> I suggest that you recheck the result when the next build is out. >>
>
> AFAIK, the bug that you found does not affect the grouping of the results
in
> this particular case, just the filtering on them afterwards in the second
> query.  However, it is *possible* that they might affect the grouping in
> general due to the nature of the bug.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>