Hi all,

I decided to post my problem here in forum, because I spent a lot of time by looking for any solution, trying everything, but without any positive results.

I have this query:

select co.name, u.surname, u.firstname, c.car_id, c.fin_str, m.name, mo.name, c.insert_date, c.offer_expiration,
(select count(*) from Offer o where o.car_id = c.car_id) as offers_count
from Car c, Users u, Company co, Manufacturer m, Model mo
where c.login = u.login and u.company_id = co.company_id(+) and c.manufacturer_id = m.ma_id(+) and c.model_id = mo.model_id(+)

I need to get offers count from Offer table for specific car id and I need to get it in my query under some alias (offers_count for example) so I can use also sorting feature later (order by offers_count).

This query works without any problems in MyEclipse Database Explorer, but it doesn't work in my Java code.

When not using (select count(*) from Offer o where o.car_id = c.car_id) as offers_count in query, everything works without any problems.

Maybe syntax should be different or something else, but I tried a lot of things and nothing worked.

If somebody would be able to help me with this, I would be very greatful.

Any more info anybody needs, just let me know.

Thanks a lot.

lumi

Anybody have a solution for following problem:

select co.name, u.surname, u.firstname, c.car_id, c.fin_str, m.name, mo.name, c.insert_date, c.offer_expiration,
(select count(*) from Offer o where o.car_id = c.car_id) as offers_count
from Car c, Users u, Company co, Manufacturer m, Model mo
where c.login = u.login and u.company_id = co.company_id(+) and c.manufacturer_id = m.ma_id(+) and c.model_id = mo.model_id(+)

I need to get offers count from Offer table for specific car id and I need to get it in my query under some alias (offers_count for example) so I can use also sorting feature later (order by offers_count).

This query works without any problems in MyEclipse Database Explorer, but it doesn't work in my Java code.

When not using (select count(*) from Offer o where o.car_id = c.car_id) as offers_count in query, everything works without any problems.

Maybe syntax should be different or something else, but I tried a lot of things and nothing worked.

If somebody would be able to help me with this, I would be very greatful.

Any more info anybody needs, just let me know.


Thanx

http://opensource.atlassian.com/project ... e/HHH-1902

vote for it!!

The only way I think is to replicate the subq in your order by

* rate if this helps *