December 10, 2013 at 12:22 pm
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
December 10, 2013 at 12:53 pm
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
December 10, 2013 at 12:54 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply