oracle ORA-00904:"可能":无效标识符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6995177/
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-00904:"MAY":INVALID IDENTIFIER
提问by mikespiteri
INSERT INTO FCR.TRANSACTION (
TRX_UNIT,
TRX_DATE,
TRX_USR,
TRX_USR_SN,
TRANSACTION_CODE,
PRODUCT_CODE,
CURRENCY_SHORT_DESCRIPTION,
AMOUNT_FC,
EXCHANGE_RATE,
AMOUNT_DC)
SELECT
SOURCE_SYSTEM_CHANNEL_CODE,
to_char(TRANSACTION_DATE, 'dd/mm/yyyy'),
USER_CODE,
USER_TRANSACTION_SERIAL_NUMBER,
TRANSACTION_CODE,
PROFITS_PRODUCT_CODE,
SHORT_DESCRIPTION,
SOURCE_AMOUNT_FC,
SOURCE_EXCHANGE_RATE,
SOURCE_AMOUNT_EUR
FROM
FCR_TRANSACTION
WHERE
TRANSACTION_DATE = to_char(02-MAY-2006, 'dd/mm/yyyy')
The above is the query which I am entering in ORACLE but it is giving me the error ORA-00904:"MAY":INVALID IDENTIFIER Can anyone help me with this please. Thanks
以上是我在 ORACLE 中输入的查询，但它给了我错误 ORA-00904:"MAY":INVALID IDENTIFIER 谁能帮我解决这个问题。谢谢
回答by APC
If TRANSACTION_DATE is a date (and I sincerely hope it is) you need to use TO_DATE to convert a string into a DATE. And because it's a string it needs to be in quotes, because that's what we do with literals.
如果 TRANSACTION_DATE 是日期（我真诚地希望它是），您需要使用 TO_DATE 将字符串转换为日期。因为它是一个字符串，所以需要用引号引起来，因为这就是我们对文字所做的。
Oh, and the other thing is, the mask needs to match the format used in the string to represent the date.
哦，另一件事是，掩码需要匹配字符串中使用的格式来表示日期。
So, the line should be:
所以，这条线应该是：
TRANSACTION_DATE = to_date('02-MAY-2006', 'dd-mon-yyyy')
回答by Ernest Friedman-Hill
You need to add quotes around 02-MAY-2006, otherwise that looks like an arithmetic expression.
您需要在 2006 年 5 月 2 日左右添加引号，否则它看起来像一个算术表达式。
回答by Jacob
TRANSACTION_DATE = to_char('02-MAY-2006', 'dd/mm/yyyy')
Put it in quotes, otherwise it is interpeted as an identifier, not a literal.
把它放在引号中，否则它会作为标识符而不是文字被插入。
回答by Kevin Burton
if you must use a string
如果必须使用字符串
To reformat you must first convert it to a date, and then to the format you desire e.g:
要重新格式化，您必须首先将其转换为日期，然后转换为您想要的格式，例如：
TRANSACTION_DATE = TO_CHAR(TO_DATE('02-MAY-2006'),'dd/mm/yyyy')
simply trying to reformat it as dd/mm/yyyy will not work
只是尝试将其重新格式化为 dd/mm/yyyy 是行不通的
回答by Uday Shankar
Use WHERE TRANSACTION_DATE = to_date('02-MAY-2006', 'dd-MON-yyyy')instead of WHERE TRANSACTION_DATE = to_char(02-MAY-2006, 'dd/mm/yyyy')
使用WHERE TRANSACTION_DATE = to_date('02-MAY-2006', 'dd-MON-yyyy')代替WHERE TRANSACTION_DATE = to_char(02-MAY-2006, 'dd/mm/yyyy')
