时间戳

首页 > 解决方案 > SQL查询一直提示：00904. 00000 - "%s: invalid identifier"

mysql - SQL查询一直提示：00904. 00000 - "%s: invalid identifier"

问题描述

SQL查询不断提示：

"ORA-00904: "DATE_FORMAT": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 11 Column: 110"

运行查询时的消息

select AUX4 as "ID",COUNT(AUX4) AS "Fails number" FROM audits where TRSTATUS <> 'SUCCESS' and DATECREATED > UPPER(DATE_FORMAT(NOW()-INTERVAL '7' DAY,"%d-%b-%y")) GROUP BY AUX4 ORDER by "Fails number" desc;

如果手动输入比较日期，则工作得很好

select AUX4,COUNT(AUX4) AS "Fails number" FROM audits where TRSTATUS <> 'SUCCESS' and DATECREATED > '25-NOV-2019' GROUP BY AUX4 ORDER by "Fails number" desc;

标签： mysqlsql

解决方案

以下是您的 oracle 日期格式。替换你的mysql NOW()到sysdate，替换DATE_FORMAT到TO_CHAR。

select AUX4 as "ID"
    ,COUNT(AUX4) AS "Fails number" 
FROM audits where TRSTATUS <> 'SUCCESS' and 
DATECREATED > UPPER(TO_CHAR(SYSDATE-INTERVAL '7' DAY,'DD-MON-YYYY')) 
GROUP BY AUX4 ORDER by "Fails number" desc;

此链接可能会帮助您将mysql查询转换为 oracle。

推荐阅读