Solved

Case expressions may only be nested to level 10.

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

632 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