ORA-00904: "P_S_LIST_MCHN_ID": invalid identifier

Question

I don't know why the second line's P_S_LIST_MCHN_ID has error of

ORA-00904: "P_S_LIST_MCHN_ID": invalid identifier

for the following query

LENGTH(P_S_LIST_MCHN_ID) IS NULL OR (
  LENGTH(P_S_LIST_MCHN_ID) > 0 AND M.MCHN_DTL_CD IN 
   (
    SELECT LIST_MCHN 
      FROM (
            SELECT REGEXP_SUBSTR (P_S_LIST_MCHN_ID, '[^,]+', 1, RN) AS LIST_MCHN
              FROM DUAL 
             CROSS JOIN (
                         SELECT ROWNUM AS RN
                           FROM (SELECT LENGTH (REGEXP_REPLACE(P_S_LIST_MCHN_ID,'[^,]+'))+1 AS MX 
                                   FROM DUAL)
                        CONNECT BY LEVEL<=MX
                        )
           ) 
   ) 
)

Please help me with this!

Answer 1

Error you got usually means that there's no such a column in a table. It is difficult to say much about it, unless you post table description.

By the way, code you wrote can be simplified to at least

 WHERE    P_S_LIST_MCHN_ID IS NULL
       OR (    P_S_LIST_MCHN_ID IS NOT NULL
           AND m.MCHN_DTL_CD IN
                  (    SELECT REGEXP_SUBSTR (P_S_LIST_MCHN_ID,
                                             '[^,]+',
                                             1,
                                             LEVEL)
                         FROM DUAL
                   CONNECT BY LEVEL <=
                              REGEXP_COUNT (P_S_LIST_MCHN_ID, ',') + 1))

• if column is null, its length is also null so you don't have to check its length - check the column itself
• all that long subquery you wrote splits P_S_LIST_MCHN_ID into rows (as it contains comma-separated values)