CannotGetResultSetMetaData

ToK

Hi there,
when I'm using sub queries like:

select cc.id, (select name from mykey ck where id = cc.mykeyid) as MyName
from myval cc where myvalkeyid = '52'

which runs proper in any other tool (Query Analyser, Excel, PHP, ACTUATE) I'm receiving an error message in BIRT:

---

cut

---

A BIRT exception occurred.
Plug-in Provider:Eclipse.org
Plug-in Name:BIRT Data Engine
Plug-in ID:org.eclipse.birt.data
Version:2.6.2.r262_v20110215
Error Code:odaconsumer.CannotGetResultSetMetaData
Error 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:Incorrect syntax near ')'.;
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ')'.

---

cut

---

Without sub select, it works. Any hints how to sort that problem out?

Thanks in advance!

ToK

Hans_vd

I'm not entirely sure about the reason behind this, but I think it has something to do with BIRT not being able to get the datatype of the name column.<br />
<br />
Also, this is not the standard way of writing such a query. Try this:<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>
select cc.id,
ck.name as MyName
from myval cc, mykey ck
where ck.id = cc.mykeyid
and myvalkeyid = '52'
</pre>
<br />
You might need an outer join instead of an inner join if your result has less rows than you expect.<br />
<br />
Regards<br />
Hans