Help with CASE statement

• cdun2

  SSCertifiable

  Points: 7893

  More actions

  February 5, 2003 at 5:36 pm

  #80277

  Hello,

  I am trying to set up a view in SQL Server 2000 in which there is a case statment that populates an aliased field with a statement in Spanish based on the integer value of another field. The code that I have so far looks like this:

  *********************************************

  Create view RC_SP_SpecEdRPTspan_vw

  AS

  Select

  SPD.Permnum, SPD.QT1, SPD.QT2, SPD.QT3, SPD.QT4,

  SPE.PrgrmNum, SPE.PrgrmDesc,

  "PrgrmDescSPANISH"= CASE

  When SPE.PrgrmNum = 1 Then PrgrmDescSPANISH ='Educación Física Adaptada'

  When SPE.PrgrmNum = 2 Then PrgrmDescSPANISH ='Servicios de Habla e Idioma'

  When SPE.PrgrmNum = 3 Then PrgrmDescSPANISH ='Especialista de Programa de Recursos'

  When SPE.PrgrmNum = 4 Then PrgrmDescSPANISH ='Clase de Día Especial'

  When SPE.PrgrmNum = 5 Then PrgrmDescSPANISH ='Otra Educación Especial'

  When SPE.PrgrmNum = 6 Then PrgrmDescSPANISH ='Programa de Estudiantes Dotados & Talentosos'

  When SPE.PrgrmNum = 7 Then PrgrmDescSPANISH ='Inmersión Estructurada de Inglés'

  When SPE.PrgrmNum = 8 Then PrgrmDescSPANISH ='Salón del Idioma Inglés'

  When SPE.PrgrmNum = 9 Then PrgrmDescSPANISH ='Alternativo'

  When SPE.PrgrmNum = 10 Then PrgrmDescSPANISH ='Título I'

  When SPE.PrgrmNum = 11 Then PrgrmDescSPANISH ='Otro Aprendiz del Idioma Inglés'

  END

  from tblRCSpecialPrgmData SPD inner join tblRCSpecialPrgmDescrip SPE On

  SPD.PrgrmNum=SPE.PrgrmNum

  *****************************************************

  When I attempt to execute this View, I get a syntax error near '=' at the first 'When' statement. Where is the syntax error? I may just create a new field in table 'tblRCSpecialPrgmDescrip' and populate a Spanish equivalent for each 'SPE.PrgrmDesc'in that field. There are only 11 records.

  Thanks,

  CSDunn

• happycat59

  One Orange Chip

  Points: 29389

  More actions

  February 5, 2003 at 5:59 pm

  #448157

  Try the following instead

  Create view RC_SP_SpecEdRPTspan_vw

  AS

  Select

  SPD.Permnum, SPD.QT1, SPD.QT2, SPD.QT3, SPD.QT4,

  SPE.PrgrmNum, SPE.PrgrmDesc,

  "PrgrmDescSPANISH"= CASE

  When SPE.PrgrmNum = 1 Then PrgrmDescSPANISH ='Educación Física Adaptada'

  When SPE.PrgrmNum = 2 Then PrgrmDescSPANISH ='Servicios de Habla e Idioma'

  When SPE.PrgrmNum = 3 Then PrgrmDescSPANISH ='Especialista de Programa de Recursos'

  When SPE.PrgrmNum = 4 Then PrgrmDescSPANISH ='Clase de Día Especial'

  When SPE.PrgrmNum = 5 Then PrgrmDescSPANISH ='Otra Educación Especial'

  When SPE.PrgrmNum = 6 Then PrgrmDescSPANISH ='Programa de Estudiantes Dotados & Talentosos'

  When SPE.PrgrmNum = 7 Then 'Inmersión Estructurada de Inglés'

  When SPE.PrgrmNum = 8 Then 'Salón del Idioma Inglés'

  When SPE.PrgrmNum = 9 Then 'Alternativo'

  When SPE.PrgrmNum = 10 Then 'Título I'

  When SPE.PrgrmNum = 11 Then 'Otro Aprendiz del Idioma Inglés'

  END

  from tblRCSpecialPrgmData SPD inner join tblRCSpecialPrgmDescrip SPE On

  SPD.PrgrmNum=SPE.PrgrmNum

• happycat59

  One Orange Chip

  Points: 29389

  More actions

  February 5, 2003 at 6:00 pm

  #448158

  Create view RC_SP_SpecEdRPTspan_vw

  AS

  Oops - missed a bit

  Select

  SPD.Permnum, SPD.QT1, SPD.QT2, SPD.QT3, SPD.QT4,

  SPE.PrgrmNum, SPE.PrgrmDesc,

  "PrgrmDescSPANISH"= CASE

  When SPE.PrgrmNum = 1 Then 'Educación Física Adaptada'

  When SPE.PrgrmNum = 2 Then 'Servicios de Habla e Idioma'

  When SPE.PrgrmNum = 3 Then 'Especialista de Programa de Recursos'

  When SPE.PrgrmNum = 4 Then 'Clase de Día Especial'

  When SPE.PrgrmNum = 5 Then 'Otra Educación Especial'

  When SPE.PrgrmNum = 6 Then 'Programa de Estudiantes Dotados & Talentosos'

  When SPE.PrgrmNum = 7 Then 'Inmersión Estructurada de Inglés'

  When SPE.PrgrmNum = 8 Then 'Salón del Idioma Inglés'

  When SPE.PrgrmNum = 9 Then 'Alternativo'

  When SPE.PrgrmNum = 10 Then 'Título I'

  When SPE.PrgrmNum = 11 Then 'Otro Aprendiz del Idioma Inglés'

  END

  from tblRCSpecialPrgmData SPD inner join tblRCSpecialPrgmDescrip SPE On

  SPD.PrgrmNum=SPE.PrgrmNum

• jmadren

  SSCrazy

  Points: 2055

  More actions

  February 6, 2003 at 11:02 am

  #448261

  Actually, you can simplify this further by using the "Simple" form of CASE. You're currently using the "Searched" form (see BOL). The Simple form of your CASE statement would then be:

  "PrgrmDescSPANISH" = CASE SPE.PrgrmNum

  When 1 Then 'Educación Física Adaptada'

  When 2 Then 'Servicios de Habla e Idioma'

  When 3 Then 'Especialista de Programa de Recursos'

  When 4 Then 'Clase de Día Especial'

  When 5 Then 'Otra Educación Especial'

  When 6 Then 'Programa de Estudiantes Dotados & Talentosos'

  When 7 Then 'Inmersión Estructurada de Inglés'

  When 8 Then 'Salón del Idioma Inglés'

  When 9 Then 'Alternativo'

  When 10 Then 'Título I'

  When 11 Then 'Otro Aprendiz del Idioma Inglés'

  END

  Jay

  
  Jay Madren