Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New to the CF scene
    Join Date
    Nov 2005
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    FrontBase: order by in subquery

    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:

    Code:
    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:
    Code:
    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:
    Code:
    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:

    Code:
    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

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    The solution that may work for you is yet another level of subquery to pull the row with max(bill_date). Something like this:

    Code:
    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)
                )
            )
        )

  • #3
    Regular Coder
    Join Date
    Apr 2008
    Location
    New Jersey
    Posts
    116
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.
    Chaos
    Lost Souls: text based RPG | MUDseek: MUD gaming search | MUDfind: MUD resource sites | Discordian Quotes


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •