Oracle ORA -932 expected Number got CHAR

Question

I am running the following query and can't seem to figure out where the error is-

select  case when month>=7 then substr(year,3,2)|| '/'||TO_NUMBER( substr(year,3,2))+1
 else to_number(substr(year,3,2))-1 || '/'|| substr(year,3,2) end as fiscal_year
FROM ( SELECT DISTINCT to_Char(extract( year from date)) as year,
 extract( month from date)as MONTH  FROM TABLE )

I want to convert year to fiscal year like 19/20, 20/21 etc

Answer 1

The operator precedence rules means the string concatenation is happening before the addition; this expression:

substr(year,3,2)|| '/'||TO_NUMBER( substr(year,3,2))+1

is evaluated as

substr(year,3,2)|| '/'||TO_NUMBER( substr(year,3,2))

and then it tries to add 1 to that string result. Hence the error you get.

You can add parentheses to make it add 1 to the year number before then concatenating that:

substr(year,3,2)|| '/'|| (TO_NUMBER( substr(year,3,2))+1)

You could also do this without so much string manipulation:

select case when extract (month from your_date) >= 7 then
    to_char(your_date, 'YY') || '/' || to_char(add_months(your_date, 12), 'YY')
  else
    to_char(add_months(your_date, -12), 'YY') || '/' || to_char(your_date, 'YY')
  end as fiscal_year
FROM (
  SELECT DISTINCT trunc(your_date, 'MM') as your_date
  FROM your_table
)

db<>fiddle

and there are other options, of course.