FrontBase: order by in subquery

[phnaargos]

Hi all,

I am not very experienced in sql, and now I am stumbling on something that I can't figure out.
I have this query:


select distinct call_destination from tt_phone_call where phone_bill_id = ( select top(0) id from tt_phone_bill where handset_id = ( select id from tt_handset where identifier = '1005385_______0613131668' ) order by bill_date desc)


and it results in error:
Syntax error 022. Expected ')' not found.
Syntax error 108. Expected ';' not found.
Exception 363 (40:000). Transaction rollback.

But if I take the statement apart, f.i. the 'right-hand' part:

select top(0) id from tt_phone_bill where handset_id = ( select id from tt_handset where identifier = '1005385_______0613131668' ) order by bill_date desc

it gives me the id I want, 1210612 in this case.

Then, if I use this id for the 'left-hand' part:


select distinct call_destination from tt_phone_call where phone_bill_id = 1210612


it works!, it gives me the correct list I was looking for.

So combining them gives a problem, but I have no idea why, the logic should be sound.

I have been googling this problem and it seems there is a restriction with using order by in a subquery, and one should use union to by-pass it. From the examples given on the web I couldn't figure out how to aplly it to my case.


Can somebody help please?

thanks,

Rob

ps: I am using FrontBase database, and the error is produced by the sql interpreter in one FrontBase's tools

[Fumigator]

The solution that may work for you is yet another level of subquery to pull the row with max(bill_date). Something like this:


select distinct call_destination
from tt_phone_call
where phone_bill_id =
( select top(0) id
from tt_phone_bill
where handset_id =
( select hs1.id
from tt_handset as hs1
where hs1.identifier = '1005385_______0613131668'
and hs1.bill_date =
( select max(hs2.bill_date)
from tt_handset as hs2
where hs2.identifier = hs1.identifier)
)
)
)

[chaosprime]

This should be in the Other Databases subforum, with FrontBase in the subject. For one thing, FrontBase's Oracle-style SELECT TOP N syntax doesn't work in MySQL.

Of course, if FrontBase is going to do stuff to you like not letting you use ORDER BY in subqueries, you could always switch to MySQL to fix that. :)