| MySql Nested Select [message #715448] |
Sun, 14 August 2011 04:18 |
 karvesh ghunsam
Messages: 95
Registered: July 2011 |
Member |
|
|
Dear All,
I am having a little issue with the dataset when it comes to nested mysql select statements.
I am currently using BIRT 3.7 shipped with Eclipse Indigo and the MySql Driver version is mysql-connector-java-5.1.17-bin.jar
I currently have 3 tables: tblMember, tblCountries and tblLoan with the structure below:
--------------
tblMember
-------------
MPK (ID)
CountryFK
--------------
---------------
tblCountries
---------------
TID (ID)
---------------
---------------
tblLoan
---------------
LPK (ID)
MFK
---------------
tblCountries has one to many on tblMember and tblMember has one to many on tblLoan
I edit my dataset and i can create a simple select statement that will return all members from the table tblMember joined to their countries in tblCountries. Also, in a nested select, i want to get the count of loans for each member. The sql is as follows:
SELECT `dbDB`.`tblcountries`.`CountryName`,
`dbDB`.`tblmember`.MPK, `dbRSSB`.`tblmember`.`MName`,
(SELECT COUNT(LPK) FROM `dbRSSB`.`tblLoan` WHERE MFK = `dbRSSB`.`tblmember`.MPK) AS X
FROM
`dbRSSB`.`tblmember` LEFT OUTER JOIN `dbRSSB`.`tblcountries` ON `dbRSSB`.`tblmember`.`CountryFK` = `dbRSSB`.`tblcountries`.TID
When i right click on the sql and i choose the menu: Run Sql, the query works fine and i can even view the results in the Sql Results tab pane.
Yet, when i want to save the dataset and when i click on ok, i get a popup with the following message:
cannot get the result set metadata
org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL Statement does not return a resultSet object
Sql error#1: you have an error in your sql syntax; check the manual that corresponds to your mysql server version for the right syntax to use near ') AS X FROM `dbRSSB`.`tblmember` LEFT OUTER JOIN `dbRSSB`.`tblcountries` at line 9;
...
Reason: BIRT Exception occured
Ok, at first it seemed that maybe my MySql server ( version 5.5.8 ) does not support the sql. But when i copy paste it in mysql query browser, the syntax is valid and the result set matches the one that BIRT returned when i did Run Sql.
Its quite awkward.. Am i doing something wrong here? Is there another way to do these select statements in BIRT??
Could anyone please advise me here? I am really stuck on that part..
Any help would be most welcomed..
Thanks in advance
Karvesh
|
|
|
Powered by
FUDForum. Page generated in 0.03832 seconds
] 
Search
Help
Register
Login
Home
