Problem with subquery

Does birt support subquery ?

Something like :

select
(select x from...) as test,
(select y from...) as test2

etc..

looks like its not supported because i have an error message when i try to use the query builder

Find more posts tagged with

Comments

johnrellis

Hey,

I got a subquery working fine on 2.2, have yet to test it on 2.3, maybe make sure your subquery works outside of birt first of all and then try it in birt?

John

BackWash

Does this simple query work for you ?

SELECT
(select 'test') as test,
(select 'test2') as test2

it is working with my mysql admin interface but using the birt sql query builder it trows an error message.

I'm using 3.2

johnrellis

I tested the simple query in eclipse 3.3.2 using birt 2.2 and eclipse 3.4 using birt 2.3 and it worked fine for me.

Whats the exception that is being thrown?

John

BackWash

sql syntax invalid or currently not supported

johnrellis

Bummer! Maybe try a sub-query that actually uses the datasource like:

select customerName from (SELECT * FROM classicmodels.customers) as c

(using the demo database or your own)

other than that it may be that you are using a version of BIRT that does not support sub queries, I don't know myself if sub query support was always present or introduced at any stage.

If i understand correct you are using eclipse 3.2 in which case it might mean you may also need to update your eclipse installation to use newer versions of birt, just remember to back EVERYTHING up before doing this though, also I am quite new to birt myself so it may be worth hanging on for a more experienced user to reply to the thread...

John

BackWash

Maybe there's another way to accomplish what I want to do :

i need this query

select distinct field1,(count field1)
from table
where id = 1
group by field1

but i know that field 1 can be 5 different string and I want the query return 0 if there's no row for id=1 and field=**** so i tough a query like :

select
(select count(field1) from table where id=1 and field1=****) as ****,
(select count(field1) from table where id=1 and field1=yyy) as yyy
...
would work but that's where i'm stuck

any other suggestion ?

BackWash

I solved my problem using unions.