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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
ErnariashCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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 PerkinsCommented:
>>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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.