I'm trying to add upon a already working query. Currently, we have only years in the column which are stored as numbers, which is determined like this:
WHEN petf.base_element_name LIKE 'XXXXX%'
THEN TO_NUMBER(TO_CHAR(peev.effective_start_date,'yyyy'))
- TO_NUMBER(regexp_replace(petf.base_element_name, '[^0-9]', ''))
Instead, I want to make it show as YYYY-MM. I've tried this:
WHEN petf.base_element_name LIKE 'XXXXX%'
THEN TO_NUMBER(TO_CHAR(peev.effective_start_date,'yyyy'))
- TO_NUMBER(regexp_replace(petf.base_element_name, '[^0-9]', '')) || '-' || TO_CHAR(peev.effective_start_date,'MM')
However, that yields the error
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
Tried this as well, same error:
THEN TO_CHAR(TO_NUMBER(TO_CHAR(peev.effective_start_date,'yyyy'))
- TO_NUMBER(regexp_replace(petf.base_element_name, '[^0-9]', ''))) || '-' || TO_CHAR(peev.effective_start_date,'MM')
The base element name for the name we're searching for always contains a number, which is extracted in the regexp and used to do the calculation based on year of start date. This works as intended, but we just want to slap the month behind it like 2023-06.
Did some searching on Google, but could not figure out how to resolve the error. Any help is much appreciated!
Other outcomes of the CASE statement were still giving a NUMBER as output. I revised all THEN clauses to give CHAR as output and then the query worked as expected.