Getting invalid identifier even though column exists-CodePudding

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)
);

Can anyone tell me why I am getting the n.stage_code invalid identifier error in the below code even though this column exists? Expected should be that this query should not give this error since this column exist in the WITH sub query clause.

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);

Database version: Oracle 18c.

CodePudding user response：

You have a USING clause which specifies stage_code as a column to join the left and right tables on and there is no stage_code from the got_new_code sub-query factoring clause as it has been aliased to new_code.

Either don't use USING and specify the join conditions manually or ensure that both sides of the join have all the columns in the USING clause:

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<>fiddle here

CodePudding user response：

You can either remove the new_code alias:

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);

or change the join from using to 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;

... which gives the joining columns twice; so you probably want to coalesce those back to a single occurrence.

db<>fiddle