AW: AW: ArrayIndexOutOfBoundsException when creating view

Hi,

thanx alot for help! I've tried it and it works.

Thanks & Greetings
  Klaudio

Klaudio Kaiser
Active Group GmbH
Hornbergstraße 49
70794 Filderstadt
Tel +49 (711) 707094-77
Fax +49 (711) 707094-89
mailto:[email protected]
http://www.active-group.de
 

> -----Ursprüngliche Nachricht-----
> Von: [email protected] [mailto:[email protected]] Im Auftrag 
> von Tobias Downer
> Gesendet: Mittwoch, 3. November 2004 22:59
> An: [email protected]
> Betreff: Re: AW: ArrayIndexOutOfBoundsException when creating view
> 
> Thanks for the test case.  I was able to reproduce the problem.
> 
> The issue is that in this query, the column aliases in the view are 
> conflicting with the identical table column names.  Defining a column 
> alias the same as a column name is normally permitted, but in 
> a view it 
> causes a column mapping problem.
> 
> The problem is not present in the following rewrite;
> 
> CREATE VIEW V_DATEN_DBA AS
> SELECT 	DA.SCHLUESSEL,
> 		DA.BENUTZERGRUPPE,
> 		DA.USERNAME,
> 		DB.KUNDE,
> 		DB.STANDZEIT,
> 		DB.EINSATZDAUER,
> 		V.VERSION
> FROM	DATEN_ALLG DA, DATEN_BEARB DB, VERSION V
> WHERE 	DA.SCHLUESSEL=DB.DATEN_ALLG_FK
> AND 	DA.SCHLUESSEL=V.REFNR
> AND 	DB.DATEN_ALLG_FK=V.REFNR;
> 
> The problem also is not present when an alias is defined that is 
> different than the column name.  It only happens when the 
> alias is the 
> same as the column name.
> 
> I will fix the bug.  Thanks,
> Toby.
> 
> 
> Klaudio Kaiser wrote:
> 
> > Hi,
> > 
> > 
> >>Did you get the error as soon as you ran the CREATE VIEW 
> command?  Did 
> >>you alter one of the tables the view is based on?
> > 
> > 
> > The error ocuured as soon as i ran the CREATE VIEW command. 
> I did not
> > alter any involved table.
> > 
> > 
> >>Does only the 'SELECT' part of the query work as expected?  eg. the 
> >>following query;
> >>
> >>SELECT 	DA.SCHLUESSEL AS SCHLUESSEL,
> >>		DA.BENUTZERGRUPPE AS BENUTZERGRUPPE,
> >>		DA.USERNAME AS USERNAME,
> >>		DB.KUNDE AS KUNDE,
> >>		DB.STANDZEIT AS STANDZEIT,
> >>		DB.EINSATZDAUER AS EINSATZDAUER,
> >>		V.VERSION AS VERSION
> >>FROM	DATEN_ALLG DA, DATEN_BEARB DB, VERSION V
> >>WHERE 	DA.SCHLUESSEL=DB.DATEN_ALLG_FK
> >>AND 	DA.SCHLUESSEL=V.REFNR
> >>AND 	DB.DATEN_ALLG_FK=V.REFNR;
> > 
> > 
> > the "SELECT...." part for itself works without problems...
> > 
> > 
> >>Could you explain layout of the 'DATEN_ALLG', 'DATEN_BEARB' and 
> >>'VERSION' tables so I can try and reproduce the error.
> > 
> > 
> > The tables have the following layout:
> > 
> > 
> ---------------------------------------------------------------------
> > CREATE TABLE DATEN_ALLG
> > (
> > 	"SCHLUESSEL" CHARACTER (29) NOT NULL,
> > 	"BENUTZERGRUPPE" VARCHAR (16) NOT NULL,
> > 	"USERNAME" VARCHAR (16) NOT NULL,
> > 	PRIMARY KEY (SCHLUESSEL)
> > );
> > 
> > CREATE TABLE DATEN_BEARB
> > (
> > 	"SCHLUESSEL" INTEGER DEFAULT UNIQUEKEY('DATEN_BEARB') NOT NULL,
> > 	"DATEN_ALLG_FK" CHARACTER (29) NOT NULL,
> > 	"KUNDE" VARCHAR (128),
> > 	"STANDZEIT" SMALLINT,
> > 	"EINSATZDAUER" SMALLINT,
> > 	PRIMARY KEY (SCHLUESSEL),
> > 	CONSTRAINT C02_DATEN_ALLG_FK FOREIGN KEY 
> (DATEN_ALLG_FK) REFERENCES
> > DATEN_ALLG (SCHLUESSEL) ON DELETE CASCADE
> > );
> > 
> > CREATE TABLE VERSION
> > (
> > 	"REFNR" CHARACTER (29) NOT NULL,
> > 	"VERSION" INTEGER NOT NULL,
> > 	"CHANGEDATE" TIMESTAMP NOT NULL,
> > 	PRIMARY KEY (REFNR),
> > 	CONSTRAINT C_CHECK_UNIQUE UNIQUE (REFNR, VERSION, CHANGEDATE)
> > );
> > 
> > CREATE VIEW V_DATEN_DBA AS
> > SELECT 	DA.SCHLUESSEL AS SCHLUESSEL,
> > 		DA.BENUTZERGRUPPE AS BENUTZERGRUPPE,
> > 		DA.USERNAME AS USERNAME,
> > 		DB.KUNDE AS KUNDE,
> > 		DB.STANDZEIT AS STANDZEIT,
> > 		DB.EINSATZDAUER AS EINSATZDAUER,
> > 		V.VERSION AS VERSION
> > FROM	DATEN_ALLG DA, DATEN_BEARB DB, VERSION V
> > WHERE 	DA.SCHLUESSEL=DB.DATEN_ALLG_FK
> > AND 	DA.SCHLUESSEL=V.REFNR
> > AND 	DB.DATEN_ALLG_FK=V.REFNR;
> > 
> ---------------------------------------------------------------------
> 
> 
> 
> ---------------------------------------------------------------
> Mckoi SQL Database mailing list  http://www.mckoi.com/database/
> To unsubscribe, send a message to [email protected]
> 


____________
Virus checked by G DATA AntiVirusKit
Version: AVK 15.0.829 from 03.11.2004
Virus news: www.antiviruslab.com



---------------------------------------------------------------
Mckoi SQL Database mailing list  http://www.mckoi.com/database/
To unsubscribe, send a message to [email protected]