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

Multiple records returned from subquery Expand / Collapse
Author
Message
Posted Tuesday, December 10, 2013 12:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 10, 2013 2:27 PM
Points: 5, Visits: 33
Still learning all abotu SQL and I am using SQL Server 2005. The issue I am having is when I the query below it displays 4 records and then get an "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression"
The fifth employee has 3 records returned fromt he subquery.

How can I rewrite to get multiple records from pay_detl with its matching record from the ret_cntrb_detl table?


declare @CheckDate datetime
set @CheckDate = '2013-12-06'

select e.pers_part_id as 'CalPERS ID', '###-##-' + right(e.empl_ssn,4) as SS#,
e.employee_id as 'EE Number', e.display_name as 'EE Last EE First',
(select top 1 pd.evnt_typ_cd + ' ' + et.evnt_short_dd from pay_detl pd, evnt_type et
where pd.internal_empl_id = rcd.internal_empl_id
and chk_dt=@CheckDate
and pd.evnt_typ_cd=et.evnt_typ_cd
and et.expiration_dt='9999-12-31'
and et.evnt_rf_id_1 in ('2','3')) 'Spec Comp',
rcd.earn_am as Amount
from empl_appt a, empl e, ret_cntrb_detl rcd
where a.internal_empl_id=e.internal_empl_id
and a.internal_empl_id=rcd.internal_empl_id
and e.pen_tier_cd in ('70001','77102')
and rcd.ret_pay_cd='09'
and rcd.gtn_run_no = (select max(gtn_run_no) from gtn_stats_hdr where gtn_typ_id='R')
--and e.employee_id=12669
order by 3
Post #1521641
Posted Tuesday, December 10, 2013 12:53 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:14 PM
Points: 2,763, Visits: 5,908
Without DDL and sample data, I suppose that you would want a comma separated value for the 'Spec Comp' column. It's explained in this article: http://www.sqlservercentral.com/articles/comma+separated+list/71700/
This should do it, but I can't be sure that it's correct because I have nothing to test.
Note that I changed your JOINS to ANSI-92 syntax.

DECLARE @CheckDate DATETIME

SET @CheckDate = '2013-12-06'

SELECT e.pers_part_id AS 'CalPERS ID',
'###-##-' + right(e.empl_ssn, 4) AS SS#,
e.employee_id AS 'EE Number',
e.display_name AS 'EE Last EE First',
STUFF((
SELECT ',' + pd.evnt_typ_cd + ' ' + et.evnt_short_dd
FROM pay_detl pd
JOIN evnt_type et ON pd.evnt_typ_cd = et.evnt_typ_cd
WHERE pd.internal_empl_id = rcd.internal_empl_id
AND chk_dt = @CheckDate
AND et.expiration_dt = '9999-12-31'
AND et.evnt_rf_id_1 IN ('2','3')
FOR XML PATH('')
), 1, 1, '') 'Spec Comp',
rcd.earn_am AS Amount
FROM empl_appt a
JOIN empl e ON a.internal_empl_id = e.internal_empl_id
JOIN ret_cntrb_detl rcd ON a.internal_empl_id = rcd.internal_empl_id
WHERE e.pen_tier_cd IN ('70001','77102')
AND rcd.ret_pay_cd = '09'
AND rcd.gtn_run_no = (
SELECT max(gtn_run_no)
FROM gtn_stats_hdr
WHERE gtn_typ_id = 'R'
)
--and e.employee_id=12669
ORDER BY 3




Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1521650
Posted Tuesday, December 10, 2013 12:54 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:24 PM
Points: 11,990, Visits: 11,007
Hi and welcome to the forums. It is nearly impossible to tell you how to fix this. It is however fairly easy to tell you the problem. Your subquery is returning more than 1 row. As a general rule you want to avoid using a subselect as the value for a column in your query. It will lead to horrible performance.

Also, you should consider using the ANSI-92 style join instead of the older style that you are using. They are easier to read and more importantly less prone to accidental cross joins.

Your main query from section would look something like this.

FROM empl_appt a
inner join empl e on a.internal_empl_id = e.internal_empl_id
inner join ret_cntrb_detl rcd on a.internal_empl_id = rcd.internal_empl_id
WHERE e.pen_tier_cd IN ( '70001', '77102' )
AND rcd.ret_pay_cd = '09'

Another habit you should try to avoid is ordering by ordinal position. You should always order by the column name. If somebody else comes along and change the column order but doesn't update the order by the query will suddenly be ordered by a different column.

Here is what I am guessing MIGHT be close to what you want.

DECLARE @CheckDate DATETIME

SET @CheckDate = '2013-12-06'

SELECT e.pers_part_id AS 'CalPERS ID',
'###-##-' + RIGHT(e.empl_ssn, 4) AS SS#,
e.employee_id AS 'EE Number',
e.display_name AS 'EE Last EE First',
sc.SpecComp,
rcd.earn_am AS Amount
FROM empl_appt a
inner join empl e on a.internal_empl_id = e.internal_empl_id
inner join ret_cntrb_detl rcd on a.internal_empl_id = rcd.internal_empl_id
inner join
(
SELECT TOP 1 pd.evnt_typ_cd + ' ' + et.evnt_short_dd as SpecComp,
pd.internal_empl_id
FROM pay_detl pd
inner join evnt_type et on pd.evnt_typ_cd = et.evnt_typ_cd
WHERE chk_dt = @CheckDate
AND et.expiration_dt = '9999-12-31'
AND et.evnt_rf_id_1 IN ( '2', '3' )
) sc on sc.internal_empl_id = rcd.internal_empl_id
WHERE e.pen_tier_cd IN ( '70001', '77102' )
AND rcd.ret_pay_cd = '09'
AND rcd.gtn_run_no = (SELECT Max(gtn_run_no)
FROM gtn_stats_hdr
WHERE gtn_typ_id = 'R')
--and e.employee_id=12669
ORDER BY e.employee_id

If that doesn't get you where you need to be, please take a few minutes and read the first link in my signature for best practices when posting questions.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1521651
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse