Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query help, please Expand / Collapse
Author
Message
Posted Tuesday, August 12, 2014 11:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 11:14 AM
Points: 1, Visits: 6
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...

Post #1602384
Posted Tuesday, August 12, 2014 11:54 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 12:55 PM
Points: 4,046, Visits: 9,200
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1602397
Posted Tuesday, August 12, 2014 3:46 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:50 PM
Points: 7,923, Visits: 9,649
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
Post #1602507
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse