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
"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.