Why do I get Invalid identifier error even though column exist WITH distinct_appl_temp AS (
  SELECT DISTINCT
    c_number
   ,j_group
   ,pi_c_dt
   ,act_can
  FROM app_fact vd
  WHERE
      vd.c_number   = 12335
    AND vd.act_can    = 1
    AND vd.pi_c_dt    = (
        SELECT
          MIN( v2.pi_c_dt )
        FROM app_fact v2
        WHERE
            v2.c_number   = vd.c_number
          AND v2.j_group    = vd.j_group
          AND v2.act_can    = vd.act_can
          AND add_months( v2.pi_c_dt,+6 ) &gt; vd.pi_c_dt
      )
  GROUP BY
    vd.c_number
   ,vd.j_group
   ,vd.pi_c_dt
   ,vd.act_can
  ORDER BY
    vd.j_group
   ,vd.act_can
) SELECT
  dt.c_number
 ,dt.j_group
 ,dt.pi_c_dt
 ,dt.act_can
 ,da.pi_c_dt
FROM distinct_appl_temp dt
 ,   app_fact da
WHERE
    dt.c_number   = da.c_number
  AND dt.j_group    = da.j_group
  AND dt.pi_c_dt    = da.pi_c_dt
  AND dt.act_can    = da.act_can
Code Review


line 3-6


Columns are not fully qualified

line 2 &amp; 21-25


DISTINCT and GROUP BY doesn't make sense in this context.  drop one

line 26-28


I wouldn't trust an ORDER BY clause in a (refactored) sub-query. Move it to the main query or drop it.

line 35-41


It's 2019.  Please use ANSI JOINs

line 34


There are duplicate column names in a SELECT statement.  Give one of them an alias.



Most likely, your problem will be fixed by (5)