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
|
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;
|
Main
webmaster@hxtt.com