列命名为 'DATE'(列名)时查询失败

Question

在Oracle 10g中，我有一个名为MYDB_PUNCHTIMEDETAILS的表，它有一个类型为TIMESTAMP的列DATE。每当我尝试执行以下语句时：

SELECT date FROM mydb_PUNCHTIMEDETAILS
WHERE  date like '19-01-15 11:56:39.000000000 AM'

它会产生以下错误：

第51行第9列出现错误
ORA-00936：缺少表达式

有人能建议如何获取数据或执行查询吗？我是Oracle的新手。

Answer 1

两种解决方案：

• 使用命名标准-不要像"Date"、"Number"等那样命名表列-要更具体，比如"Creation_Date"、"Transaction_Date">

• 使用别名，如：

  select mpd.date from mydb_punchmedetails mpd

Answer 2

将"日期"置于双引号中，

SELECT "date" FROM mydb_PUNCHTIMEDETAILS WHERE "date" like '19-01-15 11:56:39.000000000 AM'

另见小提琴

更新

虽然oracle的隐式数据类型转换允许将时间戳与字符串进行比较，但where子句可能不会像预期的那样工作，因为LIKE会比较字符串：它取决于从时间戳到varchar2的隐式转换中使用的格式，这在列值和文字之间似乎有所不同(这可能是解析时优化的结果(。从概念上讲，在瞬间精度上的近似匹配可能根本不是我们的意图。

选择精确比较(时间戳值的比较(

select "date" from test WHERE "date" = TIMESTAMP'19-01-15 11:56:39.000000000';

或者构造与所需精度匹配的like自变量：

-- ok (precision in comparison: minute)
select "date" from test WHERE "date" LIKE REPLACE(TIMESTAMP'19-01-15 11:56:39.000000000', '39.000000000', '%');

最干净的方法是在数据类型之间显式转换，并指定格式/精度：

select "date" from test WHERE TO_CHAR("date", 'YY-MM-DD HH24:MI:SS') LIKE TO_CHAR(TIMESTAMP'19-01-15 11:56:39.000000000', 'YY-MM-DD HH24:MI:SS');

更新后的sqlfiddle显示了这些以及更多的变体(有些有效，有些无效(

(感谢@WilliamRobertson指出了所讨论的问题。(

Answer 3

您应该注意，这通常不允许作为列名，因为它是用于数据类型和文字表达式(例如date '2018-08-03'(的关键字。像这样命名列的唯一方法是强制它，在创建表时和每次引用它时，使用完全相同的大小写字母，将名称用双引号括起来。

显然，这将永远导致额外的工作，并且令人困惑，因为date和timestamp是两种不同的数据类型。

此操作失败：

create table mydb_punchtimedetails
( date timestamp );
ORA-00904: invalid identifier

您可以强制Oracle跳过其正常验证：

create table mydb_punchtimedetails
( "DATE" timestamp );
insert into mydb_punchtimedetails values (timestamp '2015-01-19 11:56:39');
insert into mydb_punchtimedetails values (timestamp '2018-08-03 14:10:00');
select d."DATE" from mydb_punchtimedetails d
where  d."DATE" = timestamp '2015-01-19 11:56:39';