ORA-00932: 不一致的数据类型：oracle 存储过程中预期的 NUMBER 得到了 DATE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/48165344/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-00932: inconsistent datatypes: expected NUMBER got DATE in oracle stored procedure
提问by Andrew
I have below query which i am using in oracle stored procedure where i am getting error as
我有以下查询，我在 oracle 存储过程中使用它，我在那里收到错误
ORA-00932: inconsistent datatypes: expected NUMBER got DATE.
day_id column is Number datatype and the data is stored in this column like for e.g 20110822and ltds column is date datatype and value is stored in this column for e.g 08-AUG-17. Both the column stored the actual date.
day_id 列是数字数据类型，数据存储在此列中，例如20110822，ltds 列是日期数据类型，值存储在此列中，例如08-AUG-17。两个列都存储了实际日期。
In my stored procedure i am putting this query in variable like below:
在我的存储过程中，我将此查询放入如下变量中：
NESS_QUERY:= select t.day_id from M_TIME t
where TO_DATE (t.day_id, ''YYYYMMDD'') < trunc('''||LTDS||''') and
TO_DATE (t.day_id, ''YYYYMMDD'') >= trunc(sysdate, ''mm'') - case trunc('''||LTDS||''') when trunc(sysdate, ''mm'') then 1 else 0 end;
DBMS_OUTPUT.PUT_LINE (NESS_QUERY);
The output of DBMS_OUTPUT.PUT_LINE is below:
DBMS_OUTPUT.PUT_LINE 的输出如下：
select t.day_id from M_TIME t
where TO_DATE (t.day_id, 'YYYYMMDD') < trunc('15-DEC-17') and
TO_DATE (t.day_id, 'YYYYMMDD') >= trunc(sysdate, 'mm') - case trunc('15-DEC-17') when trunc(sysdate, 'mm') then 1 else 0 end
回答by Kaushik Nayak
You should convert day_idto datebefore comparison, using TO_DATE
您应该在比较之前转换day_id为date，使用TO_DATE
SELECT t.day_id
FROM M_TIME t
WHERE TO_DATE (t.day_id, 'YYYYMMDD') >=
TRUNC (SYSDATE, 'mm')
- CASE TRUNC (ltds) WHEN TRUNC (SYSDATE, 'mm') THEN 1 ELSE 0 END;
EDIT:
You don't need to use the ldtsvariable in your query, you may save it and use it in other places, but for query you could define it and reuse from cte.
编辑：您不需要ldts在查询中使用该变量，您可以保存它并在其他地方使用它，但是对于查询，您可以定义它并从 cte 重用它。
WITH l AS (SELECT TRUNC (LOAD_DATE) LDTS FROM JOB_EXE)
SELECT t.day_id
FROM M_TIME t CROSS JOIN l
WHERE TO_DATE (t.day_id, 'YYYYMMDD') < l.LDTS
AND TO_DATE (t.day_id, 'YYYYMMDD') >=
TRUNC (SYSDATE, 'mm')
- CASE l.LDTS WHEN TRUNC (SYSDATE, 'mm') THEN 1 ELSE 0 END
回答by Boneist
If you have any influence over the choice of datatype for this column, please get it changed to be of DATE datatype. That will make things (like the query you're attempting to run) much easier.
如果您对此列的数据类型选择有任何影响，请将其更改为 DATE 数据类型。这将使事情（例如您尝试运行的查询）变得更加容易。
As your column is not currently DATE datatype, if you want to compare the contents of the column to a date, you need to convert the column via to_date()along with an appropriate format mask. In your case, it looks like the format mask is yyyymmdd.
由于您的列当前不是 DATE 数据类型，如果您想将列的内容与日期进行比较，您需要通过to_date()适当的格式掩码转换列。在您的情况下，格式掩码看起来像yyyymmdd.
So your query should be something along the lines of:
所以你的查询应该是这样的：
select t.day_id
from m_time t
where to_date(t.day_id, 'yyyymmdd') >= trunc(sysdate, 'mm') - case trunc(ltds) when trunc(sysdate, 'mm') then 1 else 0 end;
回答by eifla001
below condition causing the error,
低于导致错误的条件，
t.day_id >= trunc(sysdate, 'mm')
day_id is a number, trunc(sysdate, 'mm') returns date datatype
day_id 是一个数字，trunc(sysdate, 'mm') 返回日期数据类型
