Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » (no subject)
(no subject) [message #715468] Sun, 14 August 2011 00:18 Go to next message
karvesh ghunsam is currently offline 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 12:53 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

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 #754928 is a reply to message #715826] Sat, 05 November 2011 05:12 Go to previous messageGo to next message
karvesh ghunsam is currently offline karvesh ghunsam
Messages: 95
Registered: July 2011
Member
Dear Jason

sorry for late reply

I am using the JDBC connector.. I am finding it almost impossible to make the nested selects..

Could you please advise me here?

Thanks in advance
Karvesh
Re: MySql Nested Select [message #754934 is a reply to message #715826] Sat, 05 November 2011 07:35 Go to previous messageGo to next message
karvesh ghunsam is currently offline karvesh ghunsam
Messages: 95
Registered: July 2011
Member
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... Smile
Re: MySql Nested Select [message #755262 is a reply to message #754934] Mon, 07 November 2011 15:15 Go to previous message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

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... :)
Previous Topic:Set Value in Dynamic Text depending on other field values
Next Topic:How to get percent of total in report
Goto Forum:
  


Current Time: Tue Jul 29 13:03:02 EDT 2014

Powered by FUDForum. Page generated in 0.02316 seconds