Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 7 of 7 total |
SELECT DISTINCT query |
Fri, Jul 13 2007 10:22 AM | Permanent Link |
Praveen | Hi,
I've migrated my DBISAM v3.30 database to ElevateDB 1.04 B5. Below DDL statements are for same table in DBISAM and ELEVATEDB. DBISAM ---------- CREATE TABLE IF NOT EXISTS "TEMP" ( "db_table_name" CHARACTER(30) NOT NULL, "level_num" INTEGER NOT NULL, "field_name" CHARACTER(30), PRIMARY KEY ("db_table_name","level_num") COMPRESS NONE LANGUAGE "ANSI Standard" SORT "Default Order" ); ELEVATEDB 1.04 B5 ----------------------- CREATE TABLE "TEMP" ( "db_table_name" VARCHAR(30) COLLATE "ANSI" NOT NULL, "level_num" INTEGER NOT NULL, "field_name" VARCHAR(30) COLLATE "ANSI", CONSTRAINT "PK" PRIMARY KEY ("db_table_name", "level_num") ) The column 'db_table_name' has table names all in UPPERCASE and fields 'level_name' and 'field_name' in LOWERCASE. In Dbisam ------------ "SELECT DISTINCT field_name FROM TEMP" - returns distinct field_name. "SELECT DISTINCT db_table_name FROM TEMP" - returns distinct db_table_name. In ElevateDB --------------- "SELECT DISTINCT field_name FROM TEMP" - returns distinct field_name. "SELECT DISTINCT db_table_name FROM TEMP" - doesn't work. All values are returned. Why ? What's the difference b/w Dbisam and ElevateDB ? In both DB's the Index has Case-Insensitive = 'No'. |
Fri, Jul 13 2007 11:24 AM | Permanent Link |
"Ole Willy Tuv" | << In ElevateDB
--------------- "SELECT DISTINCT field_name FROM TEMP" - returns distinct field_name. "SELECT DISTINCT db_table_name FROM TEMP" - doesn't work. All values are returned. Why ? What's the difference b/w Dbisam and ElevateDB ? In both DB's the Index has Case-Insensitive = 'No'. >> I think this is a bug in EDB 1.04. If you request an insensitive result set, the results are correct. AFAIK, a DISTINCT query should always return a static (insensitive) result regardless of whether a sensitive result was requested or not. Ole Willy Tuv |
Fri, Jul 13 2007 4:56 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Praveen,
<< What's the difference b/w Dbisam and ElevateDB ? In both DB's the Index has Case-Insensitive = 'No'. >> It's a bug. The workaround is to set the TEDBQuery.RequestSensitive property to False before executing the query. -- Tim Young Elevate Software www.elevatesoft.com |
Fri, Jul 13 2007 4:59 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Ole,
<< AFAIK, a DISTINCT query should always return a static (insensitive) result regardless of whether a sensitive result was requested or not. >> Actually, it can return a sensitive result set if the SELECT columns cover the entire primary key, which is what EDB is trying to do but screwed up because it only partially-matched the primary key columns. -- Tim Young Elevate Software www.elevatesoft.com |
Sat, Jul 14 2007 2:20 PM | Permanent Link |
Praveen | Tim,
<< It's a bug. The workaround is to set the TEDBQuery.RequestSensitive property to False before executing the query. >> Thanks!! -- Praveen Elevate Software www.elevatesoft.com |
Mon, Jul 16 2007 12:59 AM | Permanent Link |
Praveen | Tim,
<< Actually, it can return a sensitive result set if the SELECT columns cover the entire primary key, which is what EDB is trying to do but screwed up because it only partially-matched the primary key columns. >> So, it's a bug ? When can we expect a fix ? You mention setting TEDBQuery.Requestsensitive = False. What if i'm using TEDBDataBase. ? -- Praveen |
Mon, Jul 16 2007 4:39 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Praveen,
<< So, it's a bug ? When can we expect a fix ? >> A new build will be out by Wednesday. << You mention setting TEDBQuery.Requestsensitive = False. What if i'm using TEDBDataBase. ? >> You'll have to use a manually-created TEDBQuery component to work around this issue. You can pass it into the TEDBDatabase.Execute method: http://www.elevatesoft.com/edb1d7_tedbdatabase_execute.htm and it will be used instead by the Execute method. -- Tim Young Elevate Software www.elevatesoft.com |
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 |