sql - Oracle Merge 语句中的 Column ambiguously defined 错误

Question

我在 Oracle SQL 中遇到此查询的问题。

Errore alla riga del comando:18 Colonna:42
Report errori:
Errore SQL: ORA-00918: column ambiguously defined
00918. 00000 - "column ambiguously defined"
*Cause:
*Action:

这是查询，但我没有看到定义不明确的列:

MERGE INTO T_HPSM_CM_UBIS H USING
(SELECT A.itam_relevant_appl_code as ret,
 b.service_id,
 b.it_service,
 b.itam_user_id,
 b.itam_last_name,
 b.itam_first_name,
 b.itrm_user_id,
 b.itam_first_name,
 b.itam_last_name
FROM
 (SELECT f.itam_relevant_appl_code
  FROM T_HPSM_CM_UBIS f
  GROUP BY f.itam_relevant_appl_code
 ) A,
 t_cp_list_itam b
 WHERE A.itam_relevant_appl_code   = b.hvb_appl_code
) s ON (H.itam_relevant_appl_code = s.ret)
WHEN MATCHED THEN
UPDATE SET   H.service_id = s.service_id, H.it_service = s.it_service;

Answer 1

您在选择列表中选择了 b.itam_first_name 和 b.itam_last_name 两次。你是故意的吗？最后两个应该有 itrm 而不是 itam 吗？

SELECT A.itam_relevant_appl_code as ret,
 b.service_id,
 b.it_service,
 b.itam_user_id,
 b.itam_last_name, -- 1st occurrence
 b.itam_first_name,  -- 1st occurrence
 b.itrm_user_id,
 b.itam_first_name, -- 2nd occurrence
 b.itam_last_name -- 2nd occurrence
FROM ...

如果这不是错误，您将必须为第 2 次出现的列指定一个别名，以允许 Oracle 区分两者。

预计到达时间:仔细查看您的查询，您实际上只使用了源查询中的 3 列:

• a.itam_relevant_appl_code
• b.service_id
• b.it_service

因此您可以删除其他列，因为它们与合并无关。