Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 11 to 17 of 17 total |
views: incorrect result? |
Mon, Jun 25 2007 7:57 AM | Permanent Link |
"Harry de Boer" | 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 > > |
Mon, Jun 25 2007 4:01 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Mon, Jun 25 2007 4:45 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@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 |
Mon, Jun 25 2007 6:09 PM | Permanent Link |
"Ole Willy Tuv" | 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 |
Mon, Jun 25 2007 6:14 PM | Permanent Link |
"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 |
Mon, Jun 25 2007 6:20 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@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 |
Tue, Jun 26 2007 5:56 AM | Permanent Link |
"Harry de Boer" | 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 > > |
« Previous Page | Page 2 of 2 | |
Jump to Page: 1 2 |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |