Main   Products   Offshore Outsourcing   Customers   Partners   ContactUs  
JDBC Databases
  HXTT Access v5.2
 
  Buy Now
  Support
  Download
  Document
  FAQ
  HXTT Cobol v2.1
  HXTT DBF v5.2
  HXTT Excel v4.2
  HXTT Paradox v5.2
  HXTT Text(CSV) v5.2
  HXTT XML v1.2
Offshore Outsourcing
Oracle Data Import/Export
DB2 Data Import/Export
Sybase Data Import/Export
Free Resources
  Firewall Tunneling
  Search Indexing Robot
  Conditional Compilation
  Password Recovery for MS Access
  Password Recovery for Corel Paradox
  Checksum Tool for MD5
  Character Set Converter
  Pyramid - Poker of ZYH
   
   
   
Hongxin Technology & Trade Ltd. of Xiangtan City (abbr, HXTT)

HXTT ACCESS
More problems with views
Nikita
2007-09-12 08:08:25.0
First of all, I'd like to thank you for your support on my previous issues. But, unfortunatelly, I still experience problems querying from views.

But this time I couldn't reproduce my problem on sample database, so here is the view I have problem with:

view1 is defined as:
SELECT *
FROM Sound_players INNER JOIN (SMS_Java INNER JOIN ((3States AS 3States_2 INNER JOIN (3States AS 3States_1 INNER JOIN Phone_series ON [3States_1].state=Phone_series.Vibra) ON [3States_2].state=Phone_series.WAP_java) INNER JOIN (Games INNER JOIN (Porting LEFT JOIN Porting_Exceptions ON Porting.id=Porting_Exceptions.id) ON Games.Name=Porting.game) ON Phone_series.Group_name=Porting.group_name) ON SMS_Java.api=Phone_series.SMS_Java) ON Sound_players.player=Phone_series.Player;

When I execute query "SELECT * FROM view1" I get this exception:

Table name Porting has been declared more than one time in a same statement.java.sql.SQLException: Table name Porting has been declared more than one time in a same statement.
Error Code:212992
SQL State:34000

at com.hxtt.global.SQLState.SQLException(Unknown Source)
at com.hxtt.sql.dc.a(Unknown Source)
at com.hxtt.sql.dc.a(Unknown Source)
at com.hxtt.sql.d8.a(Unknown Source)
at com.hxtt.sql.dc.a(Unknown Source)
at com.hxtt.sql.d8.a(Unknown Source)
...


It is the same problem that was solved in release 2.1.069 but in this view it still remains.

view1 is a simplified version of this query (view2), which has another problem:
SELECT *
FROM Sound_players INNER JOIN (SMS_Java INNER JOIN ((3States AS 3States_2 INNER JOIN (3States AS 3States_1 INNER JOIN Phone_series ON [3States_1].state=Phone_series.Vibra) ON [3States_2].state=Phone_series.WAP_java) INNER JOIN (Games INNER JOIN ((((Porting INNER JOIN Port_build ON (Porting.game=Port_build.game) AND (Porting.output_dir=Port_build.output_dir)) INNER JOIN Port_subversion ON (Porting.game=Port_subversion.game) AND (Porting.output_dir=Port_subversion.output_dir)) INNER JOIN Port_version ON (Porting.game=Port_version.game) AND (Porting.output_dir=Port_version.output_dir)) LEFT JOIN Porting_Exceptions ON Porting.id=Porting_Exceptions.id) ON Games.Name=Porting.game) ON Phone_series.Group_name=Porting.group_name) ON SMS_Java.api=Phone_series.SMS_Java) ON Sound_players.player=Phone_series.Player;

When querying from this view the error looks like this:
SELECT * FROM view2
java.lang.reflect.InvocationTargetExceptionjava.sql.SQLException: java.lang.reflect.InvocationTargetException
Error Code:393216
SQL State:60000

at com.hxtt.global.SQLState.SQLException(Unknown Source)
at com.hxtt.sql.c6.a(Unknown Source)
at com.hxtt.sql.de.getParameterMetaData(Unknown Source)
...


Could you please look at this problems?

Test case is uploaded on your ftp as nikita_testcase.zip

Thanks in advance,
Nikita
Re:More problems with views
HXTT Support
2007-09-13 07:58:00.0
Checked. It seems that you're using very complicated join relation. The next package will produce the following sqls for your views.

SELECT DISTINCT Porting.game,Porting.output_dir,Max(IIf(ps.Version Is Null,0,ps.Version)) AS Version,Max(ps.subver_date) AS subver_date FROM Porting LEFT JOIN Port_max_subversions AS ps ON (Porting.output_dir=ps.output_dir Or ps.output_dir='_ALL_') AND (Porting.game=ps.game) GROUP BY Porting.game,Porting.output_dir;
SELECT Versions.game,Versions.output_dir,Versions.type,Max(Versions.version) AS Version,Max(Versions.date) AS subver_date FROM Versions GROUP BY Versions.game,Versions.output_dir,Versions.type HAVING (((Versions.type)='������������������'));
SELECT DISTINCT Porting.game,Porting.output_dir,Max(IIf(pv.Version Is Null,1,pv.Version)) AS Version,Max(pv.version_date) AS version_date FROM Porting LEFT JOIN Port_max_versions AS pv ON (Porting.game=pv.game) AND (Porting.output_dir=pv.output_dir Or pv.output_dir='_ALL_') GROUP BY Porting.game,Porting.output_dir;
SELECT Versions.game,Versions.output_dir,Versions.type,Max(Versions.version) AS Version,Max(Versions.date) AS version_date FROM Versions GROUP BY Versions.game,Versions.output_dir,Versions.type HAVING (((Versions.type)='������������'));

SELECT Games.Project_name FROM Sound_players INNER JOIN (SMS_Java INNER JOIN (3States AS [3States_2] INNER JOIN (3States AS [3States_1] INNER JOIN (Phone_series INNER JOIN (Games INNER JOIN ((((Porting INNER JOIN Port_build ON (Porting.game=Port_build.game) AND (Porting.output_dir=Port_build.output_dir)) INNER JOIN Port_subversion ON (Porting.game=Port_subversion.game) AND (Porting.output_dir=Port_subversion.output_dir)) INNER JOIN Port_version ON (Porting.game=Port_version.game) AND (Porting.output_dir=Port_version.output_dir)) LEFT JOIN Porting_Exceptions ON Porting.id=Porting_Exceptions.id) ON Games.Name=Porting.game) ON Phone_series.Group_name=Porting.group_name) ON [3States_1].state=Phone_series.Vibra) ON [3States_2].state=Phone_series.WAP_java) ON SMS_Java.api=Phone_series.SMS_Java) ON Sound_players.player=Phone_series.Player;

SELECT Games.Project_name FROM Sound_players INNER JOIN (SMS_Java INNER JOIN (3States AS [3States_2] INNER JOIN (3States AS [3States_1] INNER JOIN (Phone_series INNER JOIN (Games INNER JOIN (Porting LEFT JOIN Porting_Exceptions ON Porting.id=Porting_Exceptions.id) ON Games.Name=Porting.game) ON Phone_series.Group_name=Porting.group_name) ON [3States_1].state=Phone_series.Vibra) ON [3States_2].state=Phone_series.WAP_java) ON SMS_Java.api=Phone_series.SMS_Java) ON Sound_players.player=Phone_series.Player;


Search Key   Search by Last 50 Questions




Google
 

Address: 9 Station Rd., Xiangtan City, Hunan Province, P.R. China
Postcode: 411100
Phone: (86)731-58225727
Fax: (86)731-58225727
Email: webmaster@hxtt.com
Copyright © 1999-2011 Hongxin Technology & Trade Ltd. | All Rights Reserved. | Privacy | Legal | Sitemap