ORA-00932: inconsistent datatypes: expected NUMBER got CHAR - Error when trying to concat

Question

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!

"Currently, we have only years in the column which are stored as numbers, which is determined like this". Your revised column is CHAR not NUMBER, so if you are still storing as a NUMBER, you will generate this error. — Jonathan Willcock
– Jonathan Willcock, Commented Jun 12, 2023 at 7:34
Hi Jonathan. It makes sense, though we'd like to change the column to CHAR. I guess I will need to change the logic for all WHEN/THEN for all the outcomes at the same time so they give the same data type as output? — MaartenB
– MaartenB, Commented Jun 12, 2023 at 7:46

MaartenB · Accepted Answer · 2023-06-12 08:39:45Z

0

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.

answered Jun 12, 2023 at 8:39

MaartenB

314 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Community Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

ORA-00932: inconsistent datatypes: expected NUMBER got CHAR - Error when trying to concat

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related