[Error] Group By Expression

Hi! Firstly, sorry for my horroble English...

So, I had a table in my database with these fields: id, name.
I'm trying to create a report with these query:

SELECT SUBSTR(name, 1, ?) client_name FROM clients GROUP BY SUBSTR(name, 1, ?);

This have an error of group by:

ReportDesign (id = 1):
+ Cannot get the result set metadata.
SQL statement does not return a ResultSet object.
SQL error #1: ORA-00979: not a GROUP BY expression

However, if I substitute field name for any string (like as above), it's run perfectly!

SELECT SUBSTR('Bruno Cobra', 1, ?) client_name FROM clients GROUP BY SUBSTR('Bruno Cobra', 1, ?);

Anybody knows what I could to do??

Find more posts tagged with

Comments

Tianli

Are you using MySQL? I can not reproduce it by the same Query.
I try this in BIRT2.3.1 and 2.3.2, using MySQL5.0 and jdbc driver is com.mysql.jdbc.Driver (v3.0) and com.mysql.jdbc.Driver (v5.1)

BTW, in this query there are two ?(dataset parameters), so you need to create two dataset parameters in your dataset.

Tianli

I can reproduce it in Oracle jdbc dataset. The following sql is also working: SELECT SUBSTR(name, 1, 8) client_name FROM clients GROUP BY SUBSTR(name, 1, 8);<br />
I'm confusing why using both datafield and dataset parameter will lead the query to be invalid? It is definitely a bug of Oracle jdbc dataset, Please log this bug at <a class='bbc_url' href='https://bugs.eclipse.org/bugs/enter_bug.cgi?classification=BIRT'>https://bugs.eclipse.org/bugs/enter_bug.cgi?classification=BIRT</a><br />
<br />
Thanks

brunocobra

Hi, thanks for answers.

I'm using Oracle.
Don't you know any way to do that? I'm needing urgently...

brunocobra

I found a way to do that!

I change my query for this:
SELECT SUBSTR(name, 1, #qtd#) client_name FROM clients GROUP BY SUBSTR(name, 1, #qtd#)

In the tab "Advanced" of my dataset's properties, I put the following in "Before open" attribute:
this.queryText = String(this.queryText).replace(/#qtd#/g, params["qtd_name"]);

Yeah, it's work!