Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 10 of 14 total |
GROUP BY problem |
Sun, Apr 8 2007 1:57 PM | Permanent Link |
"Ole Willy Tuv" | The following query:
select floor((PV.rowID-1)/3)+1 as rowCellID, floor((PV.columnID-1)/3)+1 as columnCellID, PV.cellValue, count(*) as occurrences from PossibleValuesStage2 as PV group by floor((PV.rowID-1)/3)+1, floor((PV.columnID-1)/3)+1, PV.cellValue order by rowCellID, columnCellID, cellValue returns 27 groups, which is incorrect. The expected result is 81 groups. The following query (which is invalid SQL): select floor((PV.rowID-1)/3)+1 as rowCellID, floor((PV.columnID-1)/3)+1 as columnCellID, PV.cellValue, count(*) as occurrences from PossibleValuesStage2 as PV group by rowCellID, columnCellID, cellValue order by rowCellID, columnCellID, cellValue returns the correct result - 81 groups. Ole Willy Tuv |
Mon, Apr 9 2007 8:48 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Ole,
<< returns 27 groups, which is incorrect. The expected result is 81 groups. >> In the interest of speed, could you send me the table that you're using ? -- Tim Young Elevate Software www.elevatesoft.com |
Mon, Apr 9 2007 9:13 PM | Permanent Link |
"Ole Willy Tuv" | Tim,
<< In the interest of speed, could you send me the table that you're using ? >> The SQL to create the database is enclosed. 1. Create and execute the sp_Sudoko_EDB procedure 2. Execute the first query: select floor((PV.rowID-1)/3)+1 as rowCellID, floor((PV.columnID-1)/3)+1 as columnCellID, PV.cellValue, count(*) as occurrences from PossibleValuesStage2 as PV group by floor((PV.rowID-1)/3)+1, floor((PV.columnID-1)/3)+1, PV.cellValue order by rowCellID, columnCellID, cellValue Result: 27 rows 3. Execute the second query: select floor((PV.rowID-1)/3)+1 as rowCellID, floor((PV.columnID-1)/3)+1 as columnCellID, PV.cellValue, count(*) as occurrences from PossibleValuesStage2 as PV group by rowCellID, columnCellID, cellValue order by rowCellID, columnCellID, cellValue Result: 81 rows Ole Willy Tuv Attachments: sp_Sudoko_EDB_debug3.zip |
Tue, Apr 10 2007 9:54 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Ole,
<< The SQL to create the database is enclosed. >> Holy sh*t, that's a pretty wild SP. The issue is fixed now. If you run the query with an execution plan, you'll see the problem - the GROUP BY is matching up the wrong select expression. EDB tries to use an existing SELECT column expression for the grouping if it can find one, otherwise it uses an internal grouping column. BTW, the truncation and range errors will most likely be in 1.02 b2. I figured out a way to isolate them to the column assignments, which is what I was looking for. -- Tim Young Elevate Software www.elevatesoft.com |
Wed, Apr 11 2007 7:03 AM | Permanent Link |
"Ole Willy Tuv" | Tim,
<< BTW, the truncation and range errors will most likely be in 1.02 b2. I figured out a way to isolate them to the column assignments, which is what I was looking for. >> I take it you mean all types of assigment targets, i.e. columns, SQL parameters and local variables ? Ole Willy Tuv |
Wed, Apr 11 2007 7:11 AM | Permanent Link |
"Ole Willy Tuv" | Tim,
<< Holy sh*t, that's a pretty wild SP. >> I didn't figure out the Sudoko algorithms myself I've translated the EDB version from a Transact-SQL script that was posted on the Internet some time ago. I think it's a good test-case for testing/debugging the PSM implementation in EDB. Ole Willy Tuv |
Wed, Apr 11 2007 3:30 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Ole,
<< I take it you mean all types of assigment targets, i.e. columns, SQL parameters and local variables ? >> No, just columns initially. Parameters and variables are more complicated because they can literally grow in EDB. You can define a parameter or variable as just VARCHAR in EDB - it doesn't require a length. -- Tim Young Elevate Software www.elevatesoft.com |
Wed, Apr 11 2007 3:40 PM | Permanent Link |
"Ole Willy Tuv" | Tim,
<< Parameters and variables are more complicated because they can literally grow in EDB. You can define a parameter or variable as just VARCHAR in EDB - it doesn't require a length. >> Are you saying that the length of a character string declaration is not respected ? CHAR/VARCHAR without a length specification normally means CHAR/VARCHAR with length 1. Ole Willy Tuv |
Wed, Apr 11 2007 3:58 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Ole,
<< Are you saying that the length of a character string declaration is not respected ? >> No, I'm saying that specifically in the case of parameters and variables in routines, EDB allows you to specify a VARCHAR without a specific length. IOW, it can grow as required. If you set a length, then EDB will respect the length. Think of a situation where you're appending a bunch of strings to one VARCHAR variable. It is very difficult, if not impossible, to know what the resultant length should be ahead of time. EDB solves this problem by allowing you to simply not specify a length, which means that the VARCHAR can be anywhere from 0 to High(Integer) in size. << CHAR/VARCHAR without a length specification normally means CHAR/VARCHAR with length 1. >> Actually, I think that is only for CHAR. VARCHAR without a length is invalid as a type specification (but legal in EDB for parameters and variables due to the above). -- Tim Young Elevate Software www.elevatesoft.com |
Wed, Apr 11 2007 4:40 PM | Permanent Link |
"Ole Willy Tuv" | Tim,
<< Actually, I think that is only for CHAR. VARCHAR without a length is invalid as a type specification (but legal in EDB for parameters and variables due to the above). >> No, the default length of 1 (if length is not specified) is the same for both CHAR and VARCHAR. Ole Willy Tuv |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |