Query help, please

  • I am migrating a Sybase ASE installation to SQL 2008R2, and have a query that runs on the Sybase, but not SQL. Can anyone tell me how to do this in SQL:

    SELECT appl_no,

    id,

    (select f_name from person where person.id = testers.id) f_name,

    (select l_name from person where person.id = testers.id) l_name,

    approved,

    credential,

    broadening,

    getdate() 'sent'

    FROM testers

    If I run this on SQL, it returns the 'Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.' error, and I understand why. On Sybase, it runs fine.

    So..how will this need to be constructed to run on SQL?

    Thanks in advance...

  • There might be a problem with your data and you might have duplicate ids on either of your tables. A quick solution could be the following but it might not be the best option.

    SELECT appl_no,

    id,

    per.f_name,

    per.l_name,

    approved,

    credential,

    broadening,

    getdate() 'sent'

    FROM testers t

    --Might need OUTER APPLY

    CROSS APPLY(select TOP 1 f_name, l_name from person p where p.id = t.id) per

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • David Hall-426383 (8/12/2014)


    I am migrating a Sybase ASE installation to SQL 2008R2, and have a query that runs on the Sybase, but not SQL. Can anyone tell me how to do this in SQL:

    SELECT appl_no,

    id,

    (select f_name from person where person.id = testers.id) f_name,

    (select l_name from person where person.id = testers.id) l_name,

    approved,

    credential,

    broadening,

    getdate() 'sent'

    FROM testers

    If I run this on SQL, it returns the 'Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.' error, and I understand why. On Sybase, it runs fine.

    So..how will this need to be constructed to run on SQL?

    Thanks in advance...

    I'm not 100% sure what the output is supposed to be (it's a very long time since last I looked at SYBASE) but I suspect that thh query is actually a straightforward inner join:

    SELECT t.appl_no,

    t.id,

    p.fname,

    p.lname,

    t.approved,

    t.credential,

    t.broadening,

    getdate() AS sent

    FROM testers t INNER JOIN person p

    ON p.id = t.id

    Tom

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply