CODEWITHSUNDEEP
sqloracle-databasesubquerycase
sas
sas

Reputation: 9

ORACLE ORA-00904: invalid identifier error

Trying to get the 2nd to last part status but getting invalid identified on my_p in subquery. What's wrong?

SELECT  part AS my_p
       ,CASE WHEN ((SELECT MAX(status) AS status
                      FROM (SELECT status,
                                   TO_CHAR(jn_datetime, 'DD-MON-YYYY HH24:MI:SS'),
                                       RANK() OVER(ORDER BY TO_DATE(TO_CHAR(jn_datetime, 'DD-MON-YYYY HH24:MI:SS'), 'DD-MON-YYYY HH24:MI:SS') DESC) my_row_num
                              FROM master 
                             WHERE part = my_p 
                            )
                      WHERE  my_row_num = 2
                        AND  ROWNUM < 2)) = 'I' THEN 'OK'
        END
  FROM  tab1;

Upvotes: 0

Views: 1175

Answers (3)

Alex Poole
Alex Poole

Reputation: 191265

You can't refer to a column alias - my_p - in the same query except in an order-by clause, unless it's outside a subquery it's defined in (as you do with my_rownum). You're trying to use it in an inner subquery, where it isn't yet really defined.

You could give the outermost tab1 a table alias and refer to that, but as Gordon mentioned there are issues with the levels of nesting, depending partly on which version you're using. (It still fails in 11gR2, but works in 18c; I forget if that changed in 12cR1 or 12cR2.)

It also doesn't really make send to convert jn_datetime to a string and then back to a date.

You possibly want something like:

WITH cte (part, status, my_row_num) AS (
  SELECT part,
    status,
    DENSE_RANK() OVER (PARTITION BY part ORDER BY jn_datetime DESC)
  FROM master
)
SELECT t.part,
  cte.status,
  CASE WHEN cte.status = 'I' THEN 'OK' END AS flag
FROM tab1 t
LEFT JOIN cte ON cte.part = t.part AND cte.my_row_num = 2;

but it's hard to tell without sample data and results.

db<>fiddle with some brief made-up data based on what I think you're doing, your original query, this query, and just for fun, @Barbaros' query.

You might need to refine this if a part can have two master rows with the same date/time.

Upvotes: 2

Agamath Kan
Agamath Kan

Reputation: 29

        CASE WHEN ( SELECT MAX(status) AS status
                      FROM (SELECT status, part,
                                   RANK() OVER (PARTITION BY part 
                                                ORDER BY jn_datetime DESC) AS my_row_num
                              FROM master                              
                            )
                      WHERE my_row_num = 2
                        AND ROWNUM < 2
                        AND part = t1.part ) = 'I' 
             THEN 'OK'
          END AS status
  FROM  tab1 t1

Upvotes: 0

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65218

You can rearrange the query like this

SELECT  part AS my_p,
        CASE WHEN ( SELECT MAX(status) AS status
                      FROM (SELECT status, part,
                                   RANK() OVER (PARTITION BY part 
                                                ORDER BY jn_datetime DESC) AS my_row_num
                              FROM master                              
                            )
                      WHERE my_row_num = 2
                        AND ROWNUM < 2
                        AND part = t1.part ) = 'I' 
             THEN 'OK'
          END AS status
  FROM  tab1 t1

in order to prevent the error due to correlated subquery which is not nested in this case. Btw, recursive conversion TO_CHAR(), and then TO_DATE() for jn_datetime has no sense if this column is of date or timestamp type column. TO_CHAR(jn_datetime, 'DD-MON-YYYY HH24:MI:SS') is redundant.

Upvotes: 2

Related Questions