Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Case expressions may only be nested to level 10.

Posted on 2010-11-24
4
Medium Priority
?
1,538 Views
Last Modified: 2012-06-27
Hello,

I search a workaround for resolve the error Case expressions may only be nested to level 10 in SQLSERVER 2000, I haven't the error in SQL SERVER 2008 when I execute the query :
declare @sql1 nvarchar(4000)
set @sql1 = 'INSERT INTO [Ref_Personnes_RAYON]
           ([PERS_CODE]
           ,[PERS_NOM]
           ,[PERS_PRENOM]
           ,[PERS_EMAIL]
           ,[TYPE_ID])

SELECT DISTINCT
pers.PER_CODE , pers.PER_NOM,
pers.PER_PRENOM, pers.PER_EMAIL,
(case emp.EMP_CODE
when ''ASSISTANT CHEF DE RAYON'' then 1
when ''ASSISTANT CHEF DE RAYON (BAT)'' then 1
when ''CHEF DE RAYON'' then 1
when ''CHEF DE RAYON 1.ECH'' then 1
when ''CHEF DE RAYON 1er echelon'' then 1
when ''CHEF DE RAYON 2 ECH'' then 1
when ''CHEF DE RAYON 2 ECH (BAT)'' then 1
when ''CHEF DE RAYON 2.ECH'' then 1
when ''CHEF DE RAYON 2.ECH (CAN)'' then 1
when ''CHEF DE RAYON 2eme ECHELON'' then 1
when ''CHEF DE RAYON 3.ECH'' then 1
when ''MAITRE COMPAGNON'' then 1
when ''MAITRE COMPAGNON PRINCIPAL'' then 1
when ''CHEF DE TACHES'' then 2
when ''CHEF DE TACHES 1.A'' then 2
when ''CHEF DE TACHES 1.B'' then 2
when ''CHEF DE TACHES 1.ECH'' then 2
when ''CHEF DE TACHES 2E'' then 2
when ''CHEF DE TACHES PRINCIPAL'' then 2
when ''CHEF TRAVX.'' then 2
when ''CONDUCTRICE DE TACHES'' then 2
when ''CONDUCTRICE DE TACHES PRINCIPALE'' then 2
when ''INGENIEUR TACHES'' then 2
when ''INGENIEUR TACHES DEBUTANT'' then 2
when ''INGENIEUR TACHES PRINCIPAL'' then 2
when ''AIDE CHEF DE TACHES'' then 2
when ''AIDE CHEF TACHES'' then 2
when ''AIDE CHEF TRAVX'' then 2
when ''ASSISTANT CHEF DE TACHES'' then 2
when ''ASSISTANT CHEF TACHES'' then 2
when ''DIRECTEUR DE TACHES'' then 2
when ''DIRECTEUR DE TACHES BATIMENT'' then 2
when ''DIRECTEUR TACHES'' then 2
when ''CHEF COMPTABLE'' then 3
when ''COMPTABLE'' then 3
when ''COMPTABLE 1.ECH'' then 3
when ''CONTROLEUR DE GESTION'' then 3
when ''CONTROLEUSE DE GESTION'' then 3
end)


FROM ['+@Srvsource + '].['+ @Dbsource + '].[dbo].PERSONNEL pers
INNER JOIN ['+@Srvsource + '].['+ @Dbsource + '].[dbo].EMPLOIE emp ON pers.EMP_ID = emp.EMP_ID
INNER JOIN ['+@Srvsource + '].['+ @Dbsource + '].[dbo].ENTITE ent ON pers.ENT_ID = ent.ENT_ID
AND emp.ENT_ID = ent.ENT_ID
INNER JOIN ['+@Srvsource + '].['+ @Dbsource + '].[dbo].SOCIETE soc ON ent.SOC_ID = soc.SOC_ID
WHERE
(ent.ENT_CODE = ''275'')
      AND (
emp.EMP_CODE = ''ASSISTANT CHEF DE RAYON''
OR emp.EMP_CODE = ''ASSISTANT CHEF DE RAYON (BAT)''
OR emp.EMP_CODE = ''CHEF DE RAYON''
OR emp.EMP_CODE = ''CHEF DE RAYON 1.ECH''
OR emp.EMP_CODE = ''CHEF DE RAYON 1er echelon''
OR emp.EMP_CODE = ''CHEF DE RAYON 2 ECH''
OR emp.EMP_CODE = ''CHEF DE RAYON 2 ECH (BAT)''
OR emp.EMP_CODE = ''CHEF DE RAYON 2.ECH''
OR emp.EMP_CODE = ''CHEF DE RAYON 2.ECH (CAN)''
OR emp.EMP_CODE = ''CHEF DE RAYON 2eme ECHELON''
OR emp.EMP_CODE = ''CHEF DE RAYON 3.ECH''
OR emp.EMP_CODE = ''MAITRE COMPAGNON''
OR emp.EMP_CODE = ''MAITRE COMPAGNON PRINCIPAL''
OR emp.EMP_CODE = ''CHEF DE TACHES''
OR emp.EMP_CODE = ''CHEF DE TACHES 1.A''
OR emp.EMP_CODE = ''CHEF DE TACHES 1.B''
OR emp.EMP_CODE = ''CHEF DE TACHES 1.ECH''
OR emp.EMP_CODE = ''CHEF DE TACHES 2E''
OR emp.EMP_CODE = ''CHEF DE TACHES PRINCIPAL''
OR emp.EMP_CODE = ''CHEF TRAVX.''
OR emp.EMP_CODE = ''CONDUCTRICE DE TACHES''
OR emp.EMP_CODE = ''CONDUCTRICE DE TACHES PRINCIPALE''
OR emp.EMP_CODE = ''INGENIEUR TACHES''
OR emp.EMP_CODE = ''INGENIEUR TACHES DEBUTANT''
OR emp.EMP_CODE = ''INGENIEUR TACHES PRINCIPAL''
OR emp.EMP_CODE = ''AIDE CHEF DE TACHES''
OR emp.EMP_CODE = ''AIDE CHEF TACHES''
OR emp.EMP_CODE = ''AIDE CHEF TRAVX''
OR emp.EMP_CODE = ''ASSISTANT CHEF DE TACHES''
OR emp.EMP_CODE = ''ASSISTANT CHEF TACHES''
OR emp.EMP_CODE = ''DIRECTEUR DE TACHES''
OR emp.EMP_CODE = ''DIRECTEUR DE TACHES BATIMENT''
OR emp.EMP_CODE = ''DIRECTEUR TACHES''
OR emp.EMP_CODE = ''CHEF COMPTABLE''
OR emp.EMP_CODE = ''COMPTABLE''
OR emp.EMP_CODE = ''COMPTABLE 1.ECH''
OR emp.EMP_CODE = ''CONTROLEUR DE GESTION''
OR emp.EMP_CODE = ''CONTROLEUSE DE GESTION''
)'


exec (@sql1)
print (@sql1)

Thanks

bibi
0
Comment
Question by:bibi92
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 34205268
>case emp.EMP_CODE

I would put the EMP_CODE  + result value into a lookup table, and join to that

create table l_EMP_CODE_rCode ( EMP_CODE varchar(100) primary key, rCode int )

INSERT INTO l_EMP_CODE_rCode ( EMP_CODE, rCode ) VALUES ( 'MAITRE COMPAGNON PRINCIPAL' , 1 ) 
INSERT INTO l_EMP_CODE_rCode ( EMP_CODE, rCode ) VALUES ( 'MAITRE COMPAGNON' , 1 ) 
--- etc for the other values

Open in new window

you will have to create and fill table table just once.

and your query will change to:

declare @sql1 nvarchar(4000)
set @sql1 = 'INSERT INTO [Ref_Personnes_RAYON]
           ([PERS_CODE]
           ,[PERS_NOM]
           ,[PERS_PRENOM]
           ,[PERS_EMAIL]
           ,[TYPE_ID])

SELECT DISTINCT
pers.PER_CODE , pers.PER_NOM, 
pers.PER_PRENOM, pers.PER_EMAIL, 
x.rCode
FROM ['+@Srvsource + '].['+ @Dbsource + '].[dbo].PERSONNEL pers
INNER JOIN ['+@Srvsource + '].['+ @Dbsource + '].[dbo].EMPLOIE emp ON pers.EMP_ID = emp.EMP_ID 
INNER JOIN ['+@Srvsource + '].['+ @Dbsource + '].[dbo].ENTITE ent ON pers.ENT_ID = ent.ENT_ID 
AND emp.ENT_ID = ent.ENT_ID 
INNER JOIN ['+@Srvsource + '].['+ @Dbsource + '].[dbo].SOCIETE soc ON ent.SOC_ID = soc.SOC_ID
INNER JOIN l_EMP_CODE_rCode x
  ON x.EMP_CODE = emp.EMP_CODE
WHERE
(ent.ENT_CODE = ''275'') 
'


exec (@sql1)
print (@sql1)

Open in new window

0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 34206120
Or maybe:

case WHEN emp.EMP_CODE IN (''ASSISTANT CHEF DE RAYON'', ''ASSISTANT CHEF DE RAYON (BAT)'' , ''CHEF DE RAYON'', ''CHEF DE RAYON 1.ECH'', ''CHEF DE RAYON 1er echelon'', ''CHEF DE RAYON 2 ECH'', ''CHEF DE RAYON 2 ECH (BAT)'', ''CHEF DE RAYON 2.ECH'', ...)
    THEN 1
WHEN emp.EMP_CODE IN (''CHEF DE TACHES'', ''CHEF DE TACHES 1.A'', ...)
    THEN 2
WHEN emp.EMP_CODE IN (''CHEF COMPTABLE'', ''COMPTABLE'', ...)
    THEN 3
end)


I don't really see "nesting" in this code, though; kind of odd.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34206148
note that the code you posted cannot give the error message you indicated, as there were not nested CASE, as Scott indicated (good catch)
0
 

Author Closing Comment

by:bibi92
ID: 34206375
Thanks a lot regards bibi
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question