1.04b2

LS

SELECT *,  CAST((max(einde) - min(aanvang)) HOUR TO MINUTE AS VARCHAR(20))
as BestedeTijd
FROM registraties
where id_medewerker = '20020'
GROUP BY id_campagne

result in rows.  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?

Regards, Harry

Tim,

I could send the catalog and table I you need them. One question though:
must I send all tables in the db (that match the catalog), or can I just
send the one table + index file and the catalog file for investigation?

Regards, Harry


"Harry de Boer" <harry@staaf.nl> schreef in bericht
news:496EE3D2-03C8-4F8C-BF40-2D4075D38B3C@news.elevatesoft.com...
> 1.04b2
>
> LS
>
> SELECT *,  CAST((max(einde) - min(aanvang)) HOUR TO MINUTE AS VARCHAR(20))
> as BestedeTijd
> FROM registraties
> where id_medewerker = '20020'
> GROUP BY id_campagne
>
> result in rows.  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?
>
> Regards, Harry
>
>

Harry,

When I had a problem back in the EDB beta days, I was able to send Tim
the affected table files (i.e., EDBTbl, EDBIdx and EDBBlb) as well as
the catalog. That was all he needed to diagnose the issue. I would
venture to say this has not changed and that is all you would need at
this juncture as well.

--
Regards,
Jan Ferguson


Harry de Boer wrote:

<<Tim,
<<
<<I could send the catalog and table I you need them. One question
<<though:  must I send all tables in the db (that match the catalog),
<<or can I just send the one table + index file and the catalog file
<<for investigation?
<<
<<Regards, Harry
<<
<<
<<"Harry de Boer" <harry@staaf.nl> schreef in bericht
<<news:496EE3D2-03C8-4F8C-BF40-2D4075D38B3C@news.elevatesoft.com...
<<<< 1.04b2
<<<<
<<<< LS
<<<<
<<<< SELECT *,  CAST((max(einde) - min(aanvang)) HOUR TO MINUTE AS
<<<<VARCHAR(20))  as BestedeTijd
<<<< FROM registraties
<<<< where id_medewerker = '20020'
<<<< GROUP BY id_campagne
<<<<
<<<< result in rows.  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?
<<<<
<<<< Regards, Harry
<<<<
<<<<
>>

Harry

Harry,

<< I could send the catalog and table I you need them. One question though:
must I send all tables in the db (that match the catalog), or can I just
send the one table + index file and the catalog file for investigation? >>

The latter is all that is necessary.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

Ok. I guess you need them then :} Send to the elevatesoft.public.binaries
ng.

Harry

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> schreef in bericht
news:BD39EBF6-86EE-445D-B9B3-5DE47D1A0022@news.elevatesoft.com...
> Harry,
>
> << I could send the catalog and table I you need them. One question
though:
> must I send all tables in the db (that match the catalog), or can I just
> send the one table + index file and the catalog file for investigation? >>
>
> The latter is all that is necessary.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>

Harry,

<< Ok. I guess you need them then :} Send to the elevatesoft.public.binaries
>>

Okay, the issue is that you're relying on the fact that the id_medewerker
column will have a 20020 value in it *after* the grouping operations.  This
is not the case, however.

You can verify this by using a different version of the view definition as a
simple query:

SELECT *,
CAST((max(einde) - min(aanvang)) HOUR TO MINUTE AS VARCHAR(20)) as
BestedeTijd
FROM registraties
GROUP BY id_campagne
HAVING id_medewerker='20020'

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< Okay, the issue is that you're relying on the fact that the id_medewerker
column will have a 20020 value in it *after* the grouping operations.  This
is not the case, however. >>

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.

Ole Willy Tuv

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

Tim,

<< The correct and valid GROUP BY clause would be: >>

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

create view bt as
select *,
cast((max(einde) - min(aanvang)) hour to minute as varchar(20)) as
BestedeTijd
from Registraties
group by
 Id_Medewerker,
 Datum,
 Id_Campagne,
 Id_Arbeidscode,
 Id_Team,
 Geaccordeerd

select *
from bt
where Id_Medewerker = '20020'

Error:
ElevateDB Error #601 The temporary table OWTT4258045088UserTests1 is corrupt
(Cannot find row during retrieval from cache)

The query works as expected without the WHERE clause.

Ole Willy Tuv

Tim,

Ok. I now used all fields in the group by statement, but got an error (see
Ole's post).

Regards, Harry

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> schreef in bericht
news:F98C5526-8450-4D9A-8705-1B80F4B8B254@news.elevatesoft.com...
> Harry,
>
> << Ok. I guess you need them then :} Send to the
elevatesoft.public.binaries
>  >>
>
> Okay, the issue is that you're relying on the fact that the id_medewerker
> column will have a 20020 value in it *after* the grouping operations.
This
> is not the case, however.
>
> You can verify this by using a different version of the view definition as
a
> simple query:
>
> SELECT *,
> CAST((max(einde) - min(aanvang)) HOUR TO MINUTE AS VARCHAR(20)) as
> BestedeTijd
> FROM registraties
> GROUP BY id_campagne
> HAVING id_medewerker='20020'
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>