Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Case expressions may only be nested to level 10.

Posted on 2010-11-24
4
Medium Priority
?
1,548 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
  • 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

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 …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

772 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