Error message on CASE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Error message on CASE

Author

Topic

Blessed1978
Yak Posting Veteran

97 Posts

Posted - 2014-11-19 : 20:26:43

i get an error on the below when I am trying to remove duplicates. basically I can have a empid in the system more than once however I just want the empid that has emp_name, email_address populated , the ones with null i want to remove, there are however emp id's that are in the system only once that dont have emp_names that are not considered duplicate so if I say where emp_name is not null it will remove valid emp id's in the system .(I want those to stay) not removed thus I added case statements within my query

Select
CASE WHEN (count (c.emp_id)) = 1 AND i.emp_name IS NOT NULL AND c.emp_eml_address IS NOT null THEN c.emp_id
WHEN (count (c.emp_id)) > 1 THEN (SELEct distinct c.emp_id FROM employee c WHERE c.emp_name IS NOT null and c.emp_eml_address is not null )
end AS [EmpID]
,I.emp_name AS [EmpName]
,I.emp_eml_address AS [Email]
,ix.date_Start AS [EmpStartDate]
,ix.change_date AS [EmpChangeDate]
,ix.end_date As [EmpEndDate]
,j.Title AS [CEmpTitle]
from employee c (NOLOCK)
inner join emp_info I (NOLOCK) on c.key = i.key
LEFT OUTER join emp_dates ix (NOLOCK) on i.key = ix.key
GROUP BY c.emp_id, I.emp_name, I.emp_eml_address, ix.date_Start,
ix.change_date, ix.end_date, j.Title

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-11-20 : 06:36:37

Error indicates that problem is with the sub query.First execute sub query independently and check or else post some sample data.

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-11-20 : 07:29:28

[code]
WITH EmpOrder
AS
(
SELECT
c.emp_id AS [EmpID]
,I.emp_name AS [EmpName]
,I.emp_eml_address AS [Email]
,ix.date_Start AS [EmpStartDate]
,ix.change_date AS [EmpChangeDate]
,ix.end_date As [EmpEndDate]
,j.Title AS [CEmpTitle]
,ROW_NUMBER() OVER (PARTITION BY c.emp_id ORDER BY I.emp_name DESC, I.emp_eml_address DESC) AS rn
FROM employee c
JOIN emp_info I
ON c.key = i.key
LEFT JOIN emp_dates ix
ON i.key = ix.key
)
SELECT EmpID, EmpName, Email, EmpStartDate, EmpChangeDate, EmpEndDate, CEmpTitle
FROM EmpOrder
WHERE rn = 1
-- may not need this bit
OR (EmpName IS NOT NULL AND Email IS NOT NULL);
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources