I have a somewhat complex SQL query that is pulling from a table of invoices. The table being queried uses three number fields to create a date (one for day, month, and year).
我有一个有点复杂的SQL查询，它是从一个表中提取的。被查询的表使用三个数字字段来创建日期(一天、一月和一年)。
This query uses a combination of concat function calls inside of a TO_DATE function. In the SELECT part of the query there is no problem, but when I use an identical statement in the WHERE clause, I get a "ORA-01858: a non-numeric character was found where a numeric was expected" that refers to the first use of concat inside of TO_DATE in the WHERE clause.
这个查询使用TO_DATE函数内部的concat函数调用的组合。在SELECT查询的一部分没有问题,但是当我在WHERE子句中使用一个相同的声明中,我得到一个“ora - 01858:发现了一个非数字字符,数字是预期”,指的是第一次使用concat内部TO_DATE在WHERE子句中。
So, what is different about using concat and TO_DATE in a where clause?
那么，在where子句中使用concat和TO_DATE有什么区别呢?
For example, this following part of the WHERE causes the non-numeric character error
例如，以下部分将导致非数字字符错误
to_date(concat(invoice_year,concat('-',concat(invoice_month, concat('-',invoice_day)))),'YYYY-MM-DD')>add_months(sysdate,-6)
but this statement in the select evaluates just fine
但是select中的语句的值很好
to_date(concat(invoice_year,concat('-',concat(invoice_month, concat('-',invoice_day)))),'YYYY-MM-DD')as invoice_date
1 个解决方案
#1
6
There may be rows in the table that will cause an error when the TO_DATE expression is evaluated; but that do not meet the other conditions in the WHERE clause.
在计算TO_DATE表达式时，表中可能存在导致错误的行;但这并不满足WHERE子句中的其他条件。
When you put the expression in the SELECT list, it is not evaluated until a row has passed all conditions in the WHERE clause.
当您将表达式放入SELECT列表中时，直到一行已经通过WHERE子句中的所有条件，才会对其进行评估。
When you use the expression in a condition, it may be evaluated before the other conditions, so it might now be evaluated for rows where it causes the error.
当您在条件中使用表达式时，可以在其他条件之前对其进行评估，因此现在可以对导致错误的行进行评估。
Note that if you have NULLs in any of those fields for a given row, it would cause the error.
注意，如果给定行的任何字段中都有null，就会导致错误。
#1
6
There may be rows in the table that will cause an error when the TO_DATE expression is evaluated; but that do not meet the other conditions in the WHERE clause.
在计算TO_DATE表达式时，表中可能存在导致错误的行;但这并不满足WHERE子句中的其他条件。
When you put the expression in the SELECT list, it is not evaluated until a row has passed all conditions in the WHERE clause.
当您将表达式放入SELECT列表中时，直到一行已经通过WHERE子句中的所有条件，才会对其进行评估。
When you use the expression in a condition, it may be evaluated before the other conditions, so it might now be evaluated for rows where it causes the error.
当您在条件中使用表达式时，可以在其他条件之前对其进行评估，因此现在可以对导致错误的行进行评估。
Note that if you have NULLs in any of those fields for a given row, it would cause the error.
注意，如果给定行的任何字段中都有null，就会导致错误。