Query help, please

• David Hall-426383

  SSC Veteran

  Points: 288

  More actions

  August 12, 2014 at 11:18 am

  #289032

  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...

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  August 12, 2014 at 11:54 am

  #1737306

  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

• TomThomson

  SSC Guru

  Points: 104773

  More actions

  August 12, 2014 at 3:46 pm

  #1737383

  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