SQL & PL/SQL

The 1st run of this query works, but all the other subsequent runs will fail with the following error: ORA-01416: two tables cannot be outer-joined to each other. But, if you comment out a field in the select and run it, it will again run the first time but fail every time after.


Can anyone plz tell me why this is happening?



select s.stp_number,

s.stp_sequence,

s.stp_schedulearly,

s.stp_schedulelate,

s.eta,

s.stp_miles_to_parent,

s.cmp_id,

cmp.cmp_name,

o.ord_hdrnumber,

ss.stpsch_unknowntimes,

trim(nvl(cmp.cmp_address1,'')) || ' ' || trim(nvl(cmp.cmp_cityname,city.cty_name)) || ', ' || trim(nvl(cmp.cmp_state,city.cty_state)) address,

e.et_type,

city.cty_name,

s.plan_number,

parent_stp.plan_number,

decode(s.plan_number,parent_stp.plan_number,'N','Y') last_stop

from stops s

join orderheader o on o.mov_number = s.mov_number

left join company cmp on cmp.cmp_id = s.cmp_id

left join city on city.cty_code = s.cty_code

left join stop_schedule ss on ss.stp_number = s.stp_number

and sst_type in ('SAP','RAP')

and (ssst_status_type = 'ACT' or (stpsch_unknowntimes = 'Y' and ssst_status_type != 'CAN'))

left join event e on e.stp_number = s.stp_number and e.et_type in ('REL','BOB','RTE','BC')

left join stops parent_stp on parent_stp.stp_number = s.stp_parent_stp_number

where s.plan_number = 4320918

order by s.stp_sequence;


Thanks in advance,
T.G.Shankar