asked on

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

Cedric_D

Try rewrite it in form:

CASE extension
WHEN '111' THEN 'NNN'
WHEN '222' THEN 'MMM'
.........
END

Cedric_D

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

ASKER CERTIFIED SOLUTION

Ernariash

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ernariash

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

SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

Clarification:
You need to stop hardcoding you values and place them in a well indexed table.