Nested Case Statement Issue - over level 10....??

Experts:

Can someone give me a work-around or tell me what the real issue is with this SQL Script..? When I uncomment the rest of my agent's names, I get this error:

Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 125, Level 15, State 4, Line 1
Case expressions may only be nested to level 10.

--KPI SALES AGENT CALL COUNTS
select distinct
 (case when extension = '210' then 'PAULA'
           when extension = '204' then 'LINA'
           when extension = '208' then 'BEN'
           when extension = '202' then 'RETHA'
           when extension = '221' then 'QUINTIN'
           when extension = '220' then 'MARCUS'
           when extension = '219' then 'ROBERT'
           when extension = '213' then 'RICHARD'
--           when extension = '212' then 'MARIAN'
--           when extension = '206' then 'CURTIS'
--           when extension = '214' then 'MIGUEL'
--           when extension = '215' then 'ALONZO'
--           when extension = '216' then 'NATINA'
                 else 'OTHER' end ) as [SALES AGENT]
 , date as [DATE]
 , COUNT(CALLID) as [CALL COUNT]
from dell2850.report_smdrdata.dbo.tblsmdrdata
where date between '2008-10-20' and getdate()
  and importfilename like 'acd%'
  and ani like '9720%'
 and extension not in  ('***', 'S35','SYS')
group by  
  (case when extension = '210' then 'PAULA'
           when extension = '204' then 'LINA'
           when extension = '208' then 'BEN'
           when extension = '202' then 'RETHA'
           when extension = '221' then 'QUINTIN'
           when extension = '220' then 'MARCUS'
           when extension = '219' then 'ROBERT'
           when extension = '213' then 'RICHARD'
--           when extension = '212' then 'MARIAN'
--           when extension = '206' then 'CURTIS'
--           when extension = '214' then 'MIGUEL'
--           when extension = '215' then 'ALONZO'
--           when extension = '216' then 'NATINA'
                 else 'OTHER' end )
 ,date
LVL 17
MIKESoftware Solutions ConsultantAsked:
Who is Participating?
 
ErnariashConnect With a Mentor Commented:
This is a limitation of the CASE statement; you cannot have more than 10 WHENs if it is running on a linked server.
You could have a sub query with a temporary table or Table variable (@Extension, or  #Extensions) as:
Extension, Name
'210',  'PAULA'
'204' , 'LINA'
'208', 'BEN'& and so on
Then use the following query: hope it help

select distinct e.Name  as [SALES AGENT]
 , date as [DATE]
 , COUNT(CALLID) as [CALL COUNT]
from dell2850.report_smdrdata.dbo.tblsmdrdata d
left outer join #Extensions e
on d.Extension = e.Extension
where date between '2008-10-20' and getdate()
  and importfilename like 'acd%'
  and ani like '9720%'
 and extension not in  ('***', 'S35','SYS')
group by  e.Name,date

Open in new window

0
 
Cedric_DCommented:
Try rewrite it in form:

CASE extension
   WHEN '111' THEN 'NNN'
   WHEN '222' THEN 'MMM'
  .........
END
0
 
Cedric_DCommented:
Secon suggestion: to not group by so complex field, rewrite as sub-query:

SELECT name, [date], COUNT(CALLID) as [CALL COUNT]
(
SELECT
  (CASE ........ ) AS name
 , date as [DATE]
 FROM ...
) as TT
GROUP BY name, date

0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
ErnariashCommented:

For others use the IsNull function, and the Extension field for the temp table has to be unique or PK
Thanks.

--KPI SALES AGENT CALL COUNTS
select distinct isnull(e.Name, 'OTHER')  as [SALES AGENT]
 , date as [DATE]
 , COUNT(CALLID) as [CALL COUNT]
from dell2850.report_smdrdata.dbo.tblsmdrdata d
left outer join #Extensions e
on d.Extension = e.Extension
where date between '2008-10-20' and getdate()
  and importfilename like 'acd%'
  and ani like '9720%'
 and extension not in  ('***', 'S35','SYS')
group by isnull(e.Name, 'OTHER') ,date

Open in new window

0
 
Anthony PerkinsConnect With a Mentor Commented:
>>Can someone give me a work-around or tell me what the real issue is with this SQL Script..? <<
Yes.  You need to stop hardcoding you values and place them in a table.
0
 
Anthony PerkinsCommented:
Clarification:
You need to stop hardcoding you values and place them in a well indexed table.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.