Incorrect syntax near the keyword 'case-CodePudding

What is wrong with this Code:

select
ACCPMF_MDANT , ACCPMF_ACPID as " Entity ID "
from ACCPMF
where ACCPMF_BERMO = ' 202012 '

case
when ACCPMF_ASOLP = ' N ' then ' 1 '
else ' 3 '
end as " Legal proceeding status "

from ACCPMF
where ACCPMF_BERMO = ' 202012 '

i have following error:

Incorrect syntax near the keyword 'case'.

CodePudding user response：

You have put the column definition starting with case after the from and where.
Try

select 
  ACCPMF_MDANT , 
  ACCPMF_ACPID as " Entity ID " ,
  case 
    when ACCPMF_ASOLP = ' N ' then ' 1 ' 
    else ' 3 ' 
    end as " Legal proceeding status 
from 
  ACCPMF 
where 
  ACCPMF_BERMO = ' 202012 ';

CodePudding user response：

From your query I guess that ACCPMF_ASOLP is also a field of your table. The error that you have is that the WHERE clause should be after the selected columns / case instruction that you want to get; so the first FROM WHERE has to be at the end:

select 
ACCPMF_MDANT , ACCPMF_ACPID as " Entity ID ",
case
when ACCPMF_ASOLP = ' N ' then ' 1 '
else ' 3 '
end as " Legal proceeding status "
from ACCPMF
where ACCPMF_BERMO = ' 202012 '

Note that I've ignored the fact that you are comparing string values with ' x ' for example instead of '1' (without the blank spaces), I assume that ' N ', ' 1 '... are the value you really need