英文:
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR - Error when trying to concat
问题
我正在尝试在已经工作的查询基础上进行添加。目前，我们在列中只有以数字形式存储的年份，是这样确定的：
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]', ''))
而我想要它显示为YYYY-MM。我尝试了以下方法：
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')
然而，这会产生错误：
ORA-00932: 数据类型不一致：期望 NUMBER 而得到 CHAR
我也尝试了以下方法，但同样出现错误：
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')
我们要搜索的基本元素名称始终包含一个数字，在正则表达式中提取并用于根据开始日期的年份进行计算。这按预期工作，但我们只想在后面添加月份，如2023-06。
我在Google上搜索了一些信息，但无法弄清楚如何解决这个错误。非常感谢任何帮助！
英文:
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!
答案1
得分: 0
CASE语句的其他结果仍然以NUMBER作为输出。我修改了所有的THEN子句以输出CHAR，然后查询按预期工作。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论