Solved

Case expressions may only be nested to level 10.

Posted on 2010-11-24
4
1,517 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 500 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 69

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
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…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

734 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