Oracle Missing keyword in oracle issue-CodePudding

I tried finetuning the query which is as below.

select (CASE WHEN v_yearMSA <> 0 THEN to_char((1   V_CALPER_INCR2 / v_yearMSA * 2.5) / 100, '99990D999') END);

it's giving me error as

Error(786,105): PL/SQL: ORA-00905: missing keyword

CodePudding user response：

You're missing the FROM clause.

With dummy sample data:

SQL> SELECT CASE
  2            WHEN v_yearmsa <> 0
  3            THEN
  4               TO_CHAR ((1   v_calper_incr2 / v_yearmsa * 2.5) / 100,
  5                        '99990D999')
  6         END
  7    FROM test;

CASEWHENV_
----------
     0.260

SQL>

If it is PL/SQL, then you might not even need SELECT (if v_ represent local variables that got their values elsewhere):

SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2     v_yearmsa       NUMBER := 1;
  3     v_calper_incr2  NUMBER := 10;
  4     result          VARCHAR2 (20);
  5  BEGIN
  6     result :=
  7        CASE
  8           WHEN v_yearmsa <> 0
  9           THEN
 10              TO_CHAR ((1   v_calper_incr2 / v_yearmsa * 2.5) / 100,
 11                       '99990D999')
 12        END;
 13     DBMS_OUTPUT.put_line (result);
 14  END;
 15  /
0.260

PL/SQL procedure successfully completed.

SQL>

If you're now missing INTO, it means that you're in PL/SQL and - in there - selects require INTO. Something like this:

declare
  result varchar2(20);
begin
  SELECT CASE
              WHEN v_yearmsa <> 0
              THEN
                 TO_CHAR ((1   v_calper_incr2 / v_yearmsa * 2.5) / 100,
                          '99990D999')
           END
      INTO result       --> this
      FROM test;
end;
/