compilation error with oracle statement

Question

I have the following oracle statement which is giving compilation errors.

v_percent_deceased_households := CASE
WHEN NVL(v_total_households, 0) > 0 THEN 
     (CAST(NVL(v_deceased_households_count, 0) AS FLOAT(53)) 
       / 
     CAST(NVL(v_total_households, 0) AS FLOAT(53))) * 100
   ELSE 0
      END;

Errors are:

Error:

PLS-00103: Encountered the symbol "(" when expecting one of the following:. ) @ %
       The symbol ")" was substituted for "(" to continue.
Line: 317
Text: (CAST(NVL(v_deceased_households_count, 0) AS FLOAT(53)) / CAST(NVL(v_total_households, 0) AS FLOAT(53))) * 100

Error: PLS-00103: Encountered the symbol "(" when expecting one of the following:. ) @ %
Line: 317
Text: (CAST(NVL(v_deceased_households_count, 0) AS FLOAT(53)) / CAST(NVL(v_total_households, 0) AS FLOAT(53))) * 100

I cant seem to resolve this syntax error..

Answer 1

Why the CAST (... AS FLOAT(53))? Why not just:

v_percent_deceased_households := CASE
WHEN v_total_households > 0 THEN 
     (NVL(v_deceased_households_count, 0) / v_total_households) * 100
   ELSE 0
      END;

(I removed some redundant NVLs too).

Answer 2

I believe CAST has to be used in a select or 'select into' For example select cast( '22-Aug-2003' AS varchar2(30) ) from dual;

or select cast( v_deceased_households_count AS float ) into myfloat from dual;