即使列存在也獲取無效識別符號

create table test_tab1(
seq_id NUMBER(10),
e_id NUMBER(10),
jira_key VARCHAR2(20),
stage_code NUMBER(10)
);

INSERT INTO test_tab1 VALUES(1,11,'JIRA_A',2);
INSERT INTO test_tab1 VALUES(2,12,'JIRA_B',3);
COMMIT;

create table test_tab2(
seq_id NUMBER(10),
e_id NUMBER(10),
jira_key VARCHAR2(20),
stage_code NUMBER(10)
);

誰能告訴我為什么我n.stage_code invalid identifier在下面的代碼中收到錯誤，即使此列存在？預期應該是這個查詢不應該給出這個錯誤，因為這個列存在于 WITH 子查詢子句中。

WITH  got_new_code  AS
      (
        SELECT m.e_id,m.jira_key
        ,      m.stage_code AS new_code
        ,      c.code
        FROM  test_tab1 m
        CROSS APPLY (
                    SELECT LEVEL - 1 AS code
                FROM   dual
                CONNECT BY LEVEL <= m.stage_code   1
                  )      c
        WHERE m.stage_code IS NOT NULL
        ORDER BY m.e_id,m.stage_code
      )
      SELECT   *
      FROM     got_new_code n
      FULL JOIN test_tab2 t USING (e_id,jira_key,stage_code);

資料庫版本：Oracle 18c。

uj5u.com熱心網友回復：

您有一個USING子句，它指定stage_code作為一個列來連接左右表，并且子查詢因式分解子句中沒有stage_code，got_new_code因為它已別名為new_code.

要么不使用USING并手動指定連接條件，要么確保連接的兩邊都具有USING子句中的所有列：

WITH  got_new_code (e_id, jira_key, new_code, code) AS (
  SELECT m.e_id,m.jira_key
  ,      m.stage_code
  ,      c.code
  FROM  test_tab1 m
        CROSS APPLY (
          SELECT LEVEL - 1 AS code
          FROM   dual
          CONNECT BY LEVEL <= m.stage_code   1
        ) c
   WHERE m.stage_code IS NOT NULL
   ORDER BY m.e_id,m.stage_code
)
SELECT *
FROM   got_new_code n
       FULL JOIN test_tab2 t
       ON (   t.e_id       = n.e_id
          AND t.jira_key   = n.jira_key
          AND t.stage_code = n.new_code );

或者

WITH  got_new_code (e_id, jira_key, stage_code, code) AS (
  SELECT m.e_id,m.jira_key
  ,      m.stage_code
  ,      c.code
  FROM  test_tab1 m
        CROSS APPLY (
          SELECT LEVEL - 1 AS code
          FROM   dual
          CONNECT BY LEVEL <= m.stage_code   1
        ) c
   WHERE m.stage_code IS NOT NULL
   ORDER BY m.e_id,m.stage_code
)
SELECT *
FROM   got_new_code n
       FULL JOIN test_tab2 t USING (e_id,jira_key,stage_code);

db<>在這里擺弄

uj5u.com熱心網友回復：

您可以洗掉new_code別名：

WITH  got_new_code  AS
      (
        SELECT m.e_id,m.jira_key
        ,      m.stage_code
        ,      c.code
        FROM  test_tab1 m
        CROSS APPLY (
                    SELECT LEVEL - 1 AS code
                FROM   dual
                CONNECT BY LEVEL <= m.stage_code   1
                  )      c
        WHERE m.stage_code IS NOT NULL
        ORDER BY m.e_id,m.stage_code
      )
      SELECT   *
      FROM     got_new_code n
      FULL JOIN test_tab2 t USING (e_id,jira_key,stage_code);

或將連接從更改using為on：

WITH  got_new_code  AS
      (
        SELECT m.e_id,m.jira_key
        ,      m.stage_code AS new_code
        ,      c.code
        FROM  test_tab1 m
        CROSS APPLY (
                    SELECT LEVEL - 1 AS code
                FROM   dual
                CONNECT BY LEVEL <= m.stage_code   1
                  )      c
        WHERE m.stage_code IS NOT NULL
        ORDER BY m.e_id,m.stage_code
      )
      SELECT   *
      FROM     got_new_code n
      FULL JOIN test_tab2 t
      ON t.e_id = n.e_id AND t.jira_key = n.jira_key AND t.stage_code = n.new_code;

...它給出了兩次連接列；所以你可能想將它們合并回一個事件。

db<>小提琴

標籤：sql 甲骨文

上一篇：JSON_ARRAYAGG回傳值帶“\”

下一篇：重復條目的更新列