时间戳

首页 > 解决方案 > Oracle SQL - ORA-00904: "IPA"."CHECK_ID": 无效标识符

sql - Oracle SQL - ORA-00904: "IPA"."CHECK_ID": 无效标识符

问题描述

当我运行以下命令时，出现错误：ORA-00904: "IPA"."CHECK_ID": invalid identifier

但是，有问题的表确实存在并且确实有该列。有任何想法吗？

SELECT *
  FROM ap_invoices_all aia
       INNER JOIN ap_invoice_lines_all aila
          ON aia.invoice_id = aila.invoice_id
       INNER JOIN ap_invoice_distributions_all ida
          ON     aila.invoice_id = ida.invoice_id
             AND aila.line_number = ida.invoice_line_number
       INNER JOIN ap_invoice_payments_all ipa
          ON aia.invoice_id = ipa.invoice_id
       INNER JOIN "poz_suppliers_v" psv ON aia.vendor_id = psv.vendor_id
       INNER JOIN "poz_supplier_sites_v" pssv
          ON     aia.vendor_id = pssv.vendor_id
             AND aia.party_site_id = pssv.party_site_id
       INNER JOIN "gl_code_combinations" gcc
          ON ida.dist_code_combination_id = gcc.code_combination_id,
       iby_payments_all ibypa
       INNER JOIN "ap_checks_all" aca
          ON     aca.check_id = ipa.check_id
             AND aca.payment_id = ibypa.payment_id
       INNER JOIN fun_all_business_units_v fun ON aia.org_id = fun.bu_id

AP_INVOICE_PAYMENTS_ALL 定义https://docs.oracle.com/cd/E51367_01/financialsop_gs/OEDMF/AP_INVOICE_PAYMENTS_ALL_tbl.htm

如果我删除了有问题的行，那么我会收到一个错误，说AIA.ORG_ID是一个无效的标识符

但是，如果我执行以下操作，它不会出错：

SELECT *
  FROM ap_invoices_all aia
       INNER JOIN fun_all_business_units_v fun ON aia.org_id = fun.bu_id

几乎就像别名必须在加入之前一样

标签： sqloracle

解决方案

您正在混合连接类型；旧式 Oracle 连接（带逗号）和正确的 ANSI 连接。这导致以您不期望的顺序检查条件，并且一些较早的别名还不存在。

解析器首先有效地尝试自行评估这部分：

       iby_payments_all ibypa
       INNER JOIN "ap_checks_all" aca
          ON     aca.check_id = ipa.check_id
             AND aca.payment_id = ibypa.payment_id
       INNER JOIN fun_all_business_units_v fun ON aia.org_id = fun.bu_id

并且在该片段中ipa并且aia（尚）不存在，因此您会看到错误。如果该部分本身没问题，那么它将加入 ANSI 部分的重置，但它并没有走那么远，因为它已经看到了错误。（我过于简化了，试图弄清楚解析器实际上在做什么通常是一个猜谜游戏......但这就是问题的要旨。）

将逗号更改iby_payments_all ibypa为交叉连接：

...
       INNER JOIN "gl_code_combinations" gcc
          ON ida.dist_code_combination_id = gcc.code_combination_id,
       iby_payments_all ibypa
       INNER JOIN "ap_checks_all" aca
          ON     aca.check_id = ipa.check_id
             AND aca.payment_id = ibypa.payment_id
       INNER JOIN fun_all_business_units_v fun ON aia.org_id = fun.bu_id

佛

...
       INNER JOIN "gl_code_combinations" gcc
          ON ida.dist_code_combination_id = gcc.code_combination_id
       CROSS JOIN iby_payments_all ibypa
       INNER JOIN "ap_checks_all" aca
          ON     aca.check_id = ipa.check_id
             AND aca.payment_id = ibypa.payment_id
       INNER JOIN fun_all_business_units_v fun ON aia.org_id = fun.bu_id

这似乎等同于您正在做的事情；虽然如果该表也有连接条件，它也应该是内部连接，但显然......

推荐阅读