Solved

Case expressions may only be nested to level 10.

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

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 142

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

861 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now