oraclecaseidentifierora-00904

ORA-00904: "PSTATUSTYPE": invalid identifier 00904. 00000 - "%s: invalid identifier" *Cause: *Action: Error at Line: 67 Column: 11

When I try to run the below SQL:

SELECT  
p_mv.created_by                            createby, 
from  pipeline             p_mv, 
pipeline             p_con, 

route route_s , 

 where  
    "pstatustype"='CTA'
     and
    case when pstatustype='CTA' then p_con.created_date
           when pstatustype='VAD' then route_s.orgn_vsl_arvl_date
           when pstatustype='ETA' then route_s.arrival_date
           else null -- default anyway
      end Between to_date('&1','DD-MON-YYYY:HH24:MI:SS')
              AND to_date('&2','DD-MON-YYYY:HH24:MI:SS');

I am getting an error for psstatustype column:

ORA-00904: "PSTATUSTYPE": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action: Error at Line: 67 Column: 11

psstatustype is not present at the DB end. I'm creating this on the fly for the case distribution under where clause. Please help!!

Regards, fuko

Solution

  • "it's a parameter."

    Your posted SQL is mangled so this is not a working example (for instance you have a cross join with no join conditions so it will return a Cartesian product). Anyway it shows you the sort of thing you need to do:

    SELECT  
     ....
from  pipeline             p_mv, 
      pipeline             p_con, 
      route route_s , 
 where  
    case '&pstatustype'
           when 'CTA' then p_con.created_date
           when 'VAD' then route_s.orgn_vsl_arvl_date
           when 'ETA' then route_s.arrival_date
           else null -- default anyway
      end 
      between to_date('&1','DD-MON-YYYY:HH24:MI:SS')
              AND to_date('&2','DD-MON-YYYY:HH24:MI:SS');

    This uses the SQL*Plus substitution variable syntax &pstatustype which will prompt you to enter a value each time you run the query. Different clients may demand a different way of specifying and populating parameters.