RA-00904无效的标识符错误，即使存在列名也是如此 [英] RA-00904 invalid identifier error even when column name exists

问题描述

我正在sqldeveloper中运行一个oracle查询:

I am running an oracle query in sqldeveloper:

merge into invoices c using (select CUSTOMER_ID, INVOICE_NUMBER, INVOICE_DATE from dual where INVOICE_NUMBER = '123'
and CUSTOMER_ID = '456' and INVOICE_DATE = '19-APR-12') cd
on (c.INVOICE_NUMBER = cd.INVOICE_NUMBER)
  when not matched then
        insert (c.CUSTOMER_ID, c.INVOICE_NUMBER, c.INVOICE_DATE)
        values ('987', '654','179-APR-12')

我一直为RA-00904 INVOICE_DATE列获取RA-00904无效标识符，即使该列存在.我已经通过运行describe invoices命令进行了验证，然后实际复制了列名:

I keep getting a RA-00904 invalid identifier for the RA-00904 INVOICE_DATE column, even though that column exists. I have verified by running the describe invoices command and then actually copying the column name:

describe invoices;
Name             
----------------
CUSTOMER_ID      
INVOICE_NUMBER   
INVOICE_DATE   

这是怎么回事?

解决方案

瓦迪姆和贾斯汀是正确的.我通过用表名替换对偶来解决了这个问题:

Vadim and Justin are correct. I fixed the problem by replacing dual with the table name:

merge into invoices c using (select CUSTOMER_ID, INVOICE_NUMBER, INVOICE_DATE from invoices where INVOICE_NUMBER = '123'
    and CUSTOMER_ID = '456' and INVOICE_DATE = '19-APR-12') cd
    on (c.INVOICE_NUMBER = cd.INVOICE_NUMBER)
      when not matched then
            insert (c.CUSTOMER_ID, c.INVOICE_NUMBER, c.INVOICE_DATE)
            values ('987', '654','179-APR-12')

推荐答案

在USING子句中，您正在对表DUAL中的列CUSTOMER_ID, INVOICE_NUMBER, INVOICE_DATE进行SELECT.该表只有一列DUMMY，因此会出现错误.

In the USING clause, you're doing a SELECT of columns CUSTOMER_ID, INVOICE_NUMBER, INVOICE_DATE from table DUAL. This table has only one column, DUMMY, hence the error.

这篇关于RA-00904无效的标识符错误，即使存在列名也是如此的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！