| (no subject) [message #715468] |
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
|
|
|
| Re: MySql Nested Select [message #715826 is a reply to message #715468] |
Mon, 15 August 2011 16:53  |
|
Are you using the JDBC Database Connection for Query Builder or the
textual JDBC Data Source?
Jason
On 8/14/2011 12:18 AM, karvesh wrote:
> 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
>
|
|
|
|
|
| Re: MySql Nested Select [message #755262 is a reply to message #754934] |
Mon, 07 November 2011 20:15 |
|
Glad you got it working. BTW the way you ended up doing it is the best
approach.
Jason
On 11/5/2011 7:35 AM, karvesh wrote:
> Dear Jason
>
> no worry i managed to find out how to do it a neater way.
> I used a nested table that binds its parameter to the outer table.
> The nested table is then simply bound to a dataset that will do the
> nested sql statement
>
> There is so much to discover from BIRT... :)
|
|
|
Powered by
FUDForum. Page generated in 0.03559 seconds
] 
Search
Help
Register
Login
Home
