SQL - missing keyword in case when syntax

I am getting this error message




missing keyword




Any suggestions? thanks 



CASE WHEN 

     substr(M1.M_GL_LINE_NO,length(M1.M_GL_LINE_NO - 4),4)>=0 

         and substr(M1.M_GL_LINE_NO,length(M1.M_GL_LINE_NO - 4),4) < 4000 

         then 'ASSET'

ELSE CASE WHEN 

         substr(M1.M_GL_LINE_NO,length(M1.M_GL_LINE_NO - 4),4)>=4000 

            and substr(M1.M_GL_LINE_NO,length(M1.M_GL_LINE_NO - 4),4) < 8000 

         then 'LIABILITY' 

ELSE CASE WHEN

          substr(M1.M_GL_LINE_NO,length(M1.M_GL_LINE_NO - 4),4)>=8000 

           and substr(M1.M_GL_LINE_NO,length(M1.M_GL_LINE_NO - 4),4) < 9000 

          then 'OFF BALANCE SHEET ASSET' 

ELSE CASE WHEN 

         substr(M1.M_GL_LINE_NO,length(M1.M_GL_LINE_NO - 4),4)>=9000 

         and substr(M1.M_GL_LINE_NO,length(M1.M_GL_LINE_NO - 4),4) < 10000 

         then 'OFF BALANCE SHEET LIABILITY' end as ASSET_TYPE,


Answers

I think you are using more "CASE" word in your case statement. Remove "ELSE CASE" after each "Then". Refer this Oracle Documentation



CASE WHEN 

     substr(M1.M_GL_LINE_NO,length(M1.M_GL_LINE_NO - 4),4)>=0 

         and substr(M1.M_GL_LINE_NO,length(M1.M_GL_LINE_NO - 4),4) < 4000 

         then 'ASSET'

     WHEN 

         substr(M1.M_GL_LINE_NO,length(M1.M_GL_LINE_NO - 4),4)>=4000 

            and substr(M1.M_GL_LINE_NO,length(M1.M_GL_LINE_NO - 4),4) < 8000 

         then 'LIABILITY' 

     WHEN

          substr(M1.M_GL_LINE_NO,length(M1.M_GL_LINE_NO - 4),4)>=8000 

           and substr(M1.M_GL_LINE_NO,length(M1.M_GL_LINE_NO - 4),4) < 9000 

          then 'OFF BALANCE SHEET ASSET' 

    WHEN 

         substr(M1.M_GL_LINE_NO,length(M1.M_GL_LINE_NO - 4),4)>=9000 

         and substr(M1.M_GL_LINE_NO,length(M1.M_GL_LINE_NO - 4),4) < 10000 

         then 'OFF BALANCE SHEET LIABILITY' 

 END as ASSET_TYPE,


The general syntax will be



CASE  

   WHEN col = 1 THEN 'Active' 

   WHEN col = 2 THEN 'Inactive' 

   WHEN col = 3 THEN 'Terminated' 

END AS StatusText


Answers

the solution is : 



case when substr(M1.M_GL_LINE_NO,length(M1.M_GL_LINE_NO - 4),4)>=0 and substr(M1.M_GL_LINE_NO,length(M1.M_GL_LINE_NO - 4),4) < 4000 then 'ASSET'

     when substr(M1.M_GL_LINE_NO,length(M1.M_GL_LINE_NO - 4),4)>=4000 and substr(M1.M_GL_LINE_NO,length(M1.M_GL_LINE_NO - 4),4) < 8000 then 'LIABILITY' 

     when substr(M1.M_GL_LINE_NO,length(M1.M_GL_LINE_NO - 4),4)>=8000 and substr(M1.M_GL_LINE_NO,length(M1.M_GL_LINE_NO - 4),4) < 9000 then 'OFF BALANCE SHEET ASSET' 

     when substr(M1.M_GL_LINE_NO,length(M1.M_GL_LINE_NO - 4),4)>=9000 and substr(M1.M_GL_LINE_NO,length(M1.M_GL_LINE_NO - 4),4) < 10000 then 'OFF BALANCE SHEET LIABILITY' 

else '' end as ASSET_TYPE,


thanks