SQL - missing keyword in case when syntax

Question

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,

Answer 1

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 

Answer 2

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