Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 6 of 6 total |
sql error or wrong interpretation? |
Thu, Jan 24 2008 1:50 PM | Permanent Link |
"Harry de Boer" | LS
This is correct (resulting in 168 rows) select * from regd join gebr on gebr.id_gebruiker = regd.id_gebruiker where id_team = 77 This is not I guess; because it misses 'gebr' after the keyword ON (resulting in 32758 rows) select * from regd join gebr on id_gebruiker = regd.id_gebruiker where id_team = 77 The team only has two id_gebruiker. If I use a team with hunderd id_gebruiker then I get millions of records (that's how I discovered this So, is this correct sql after all and gives a correct result (other then what I expected), or should an error be expected? Regards, Harry |
Thu, Jan 24 2008 2:40 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Harry,
<< So, is this correct sql after all and gives a correct result (other then what I expected), or should an error be expected? >> Could you post the two table definitions in SQL ? Thanks, -- Tim Young Elevate Software www.elevatesoft.com |
Thu, Jan 24 2008 5:54 PM | Permanent Link |
"Harry de Boer" | Tim,
> Could you post the two table definitions in SQL ? Regards, Harry EXECUTE IMMEDIATE 'CREATE TABLE "GEBR" ( "Id_Gebruiker" INTEGER NOT NULL, "Id_Team" INTEGER NOT NULL, "Naam_Gebruiker" VARCHAR(30) COLLATE "ANSI" NOT NULL, "Dienstverband" VARCHAR(2) COLLATE "ANSI", "Id_Vestiging" INTEGER NOT NULL, "Id_Werkgever" INTEGER NOT NULL, "Min_Uren" SMALLINT NOT NULL, "Max_Uren" SMALLINT, "Datum_Geboorte" DATE, "Geslacht" VARCHAR(1) COLLATE "ANSI", "Password" VARCHAR(10) COLLATE "ANSI", "Aut_Registreren" BOOLEAN, "Aut_Accorderen" BOOLEAN, "Aut_RegistrerenPlus" BOOLEAN, "Aut_Corrigeren" BOOLEAN, "Aut_Periode_Afsluiten" BOOLEAN, "Aut_Klant_Proj_Camp_Uurcode" BOOLEAN, "Aut_Team_Gebruiker" BOOLEAN, "Afwezig_Ziek" BOOLEAN, "Afwezig_Overig" BOOLEAN, "SoFiNummer" INTEGER, CONSTRAINT "PrimaryKey" PRIMARY KEY ("Id_Gebruiker") ) VERSION 1 UNENCRYPTED INDEX PAGE SIZE 4096 BLOB BLOCK SIZE 512 MAX ROW BUFFER SIZE 32768 MAX INDEX BUFFER SIZE 65536 MAX BLOB BUFFER SIZE 32768'; EXECUTE IMMEDIATE 'CREATE INDEX "idxTeam" ON "GEBR" ("Id_Team")'; EXECUTE IMMEDIATE 'CREATE TABLE "REGD" ( "Id_gebruiker" INTEGER NOT NULL, "Datum" DATE NOT NULL, "Tijd_begin" TIME NOT NULL, "Tijd_einde" TIME, "Geaccordeerd" BOOLEAN, "Afgesloten" BOOLEAN, CONSTRAINT "PrimaryKey" PRIMARY KEY ("Id_gebruiker", "Datum") ) DESCRIPTION ''Registratie dag'' VERSION 1 UNENCRYPTED INDEX PAGE SIZE 4096 BLOB BLOCK SIZE 512 MAX ROW BUFFER SIZE 32768 MAX INDEX BUFFER SIZE 65536 MAX BLOB BUFFER SIZE 32768'; "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> schreef in bericht news:96533EBC-20F5-421B-A343-BE6A93173387@news.elevatesoft.com... > Harry, > > << So, is this correct sql after all and gives a correct result (other then > what I expected), or should an error be expected? >> > > Could you post the two table definitions in SQL ? > > Thanks, > > -- > Tim Young > Elevate Software > www.elevatesoft.com > > |
Fri, Jan 25 2008 1:26 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Harry,
Thanks. I actually could have answered the question without them, but I was a little busy yesterday and didn't have my head straight. Because you're using the REGD table as the first table in the query, any columns without an explicit table qualifier name will use the REGD table as the table to first look for the column. So, basically your query ends up doing a join on the same column in the same table. -- Tim Young Elevate Software www.elevatesoft.com |
Sat, Jan 26 2008 7:28 AM | Permanent Link |
"Ole Willy Tuv" | Tim,
<< Because you're using the REGD table as the first table in the query, any columns without an explicit table qualifier name will use the REGD table as the table to first look for the column. >> The correct SQL behavior would be to raise an "ambiguous column reference" exception. Ole Willy Tuv |
Mon, Jan 28 2008 2:44 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Ole,
<< The correct SQL behavior would be to raise an "ambiguous column reference" exception. >> Yes, I understand. I still have that on my to-do list, believe it or not, along with the GROUP BY warning, etc. I really need to go ahead and get those in EDB. -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Friday, May 3, 2024 at 08:07 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |