Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table (10.2.0)
ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table [message #340814] Thu, 14 August 2008 08:31 Go to next message
oldgun
Messages: 1
Registered: August 2008
Junior Member
Hi All,

I have a long select clause with many joins. And SQLPLUS gives me error: ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table. If I remove a outer join, for example, the last one, it is ok. If I turn the last join into a regular join, it also ok.
Any idea? many thanks.

Here is the real query:

select distinct
cm.*

from car_move cm
join move_plan mp
on (mp.move_plan_id = cm.move_plan_id)
join stop ostop
on ( ostop.car_move_id = cm.car_move_id
and ostop.tms_stop_seq =
(select min(tms_stop_seq)
from stop
where stop.car_move_id = cm.car_move_id))
join stop dstop
on ( dstop.car_move_id = cm.car_move_id
and dstop.tms_stop_seq =
(select max(tms_stop_seq)
from stop
where stop.car_move_id = cm.car_move_id))
join stop
on (stop.car_move_id = cm.car_move_id)
join adrmst a
on (a.adr_id = stop.adr_id)
join adrmst oa
on (oa.adr_id = ostop.adr_id)
join adrmst da
on (da.adr_id = dstop.adr_id)
join (select coalesce(
( select 'Multiple'
from shipment sh1
join ship_move sm1
on sm1.ship_id = sh1.ship_id
where sm1.car_move_id = cm1.car_move_id
group by sm1.car_move_id
having count(distinct coalesce(sh1.ses_grp_nam,'null')) > 1 ),
( select distinct sh1.ses_grp_nam
from shipment sh1
join ship_move sm1
on sh1.ship_id = sm1.ship_id
where sm1.car_move_id = cm1.car_move_id )) ses_grp_nam,
coalesce(
( select 'Multiple'
from shipment sh1
join ship_move sm1
on sm1.ship_id = sh1.ship_id
where sm1.car_move_id = cm1.car_move_id
group by sm1.car_move_id
having count(distinct coalesce(sh1.ses_tag,'null')) > 1 ),
( select distinct sh1.ses_tag
from shipment sh1
join ship_move sm1
on sh1.ship_id = sm1.ship_id
where sm1.car_move_id = cm1.car_move_id )) ses_tag,
car_move_id
from car_move cm1 ) ses
on cm.car_move_id = ses.car_move_id
join (select decode(stem_car.car_move_id,NULL,'false','true') vists_cp,
car_move.car_move_id
from (select distinct car_move.car_move_id
from car_move
join ship_move sm_cp1
on sm_cp1.car_move_id = car_move.car_move_id
join ship_move sm_cp2
on sm_cp1.ship_id = sm_cp2.ship_id
join car_move leg_car
on leg_car.car_move_id = sm_cp2.car_move_id
and leg_car.car_move_typ = 'CP'
where car_move.car_move_typ = 'STEM') stem_car
right outer join car_move
on stem_car.car_move_id = car_move.car_move_id) cm_cp1
on cm.car_move_id = cm_cp1.car_move_id
join (select decode(stem_car.car_move_id,NULL,'false','true') vists_dp,
car_move.car_move_id
from (select distinct car_move.car_move_id
from car_move
join ship_move sm_dp1
on sm_dp1.car_move_id = car_move.car_move_id
join ship_move sm_dp2
on sm_dp1.ship_id = sm_dp2.ship_id
join car_move leg_car
on leg_car.car_move_id = sm_dp2.car_move_id
and leg_car.car_move_typ = 'DP'
where car_move.car_move_typ = 'STEM') stem_car
right outer join car_move
on stem_car.car_move_id = car_move.car_move_id) cm_dp2
on cm.car_move_id = cm_dp2.car_move_id
left outer join move_plan_veh mpv
on mpv.usage = 'PRIMARY'
and mp.move_plan_id = mpv.move_plan_id
left outer join ship_move sm
on cm.car_move_id = sm.car_move_id
left outer join shipment s
on (s.ship_id = sm.ship_id)
left outer join shipment_line sl
on (sl.ship_id = s.ship_id)
left outer join ord_line ol
on ( ol.ordnum = sl.ordnum
and ol.ordsln = sl.ordsln
and ol.ordlin = sl.ordlin
and ol.ordnum = sl.ordnum
and ol.client_id = sl.client_id
and ol.wh_id = sl.wh_id)
left outer join ord o
on ( o.ordnum = ol.ordnum
and o.client_id = ol.client_id
and o.wh_id = ol.wh_id)
left outer join client_grp_client cgc
on cgc.client_id = o.client_id
left outer join trnsp_mode
on trnsp_mode.trnsp_mode = cm.trans_mode
left outer join cal_schd_trip
on cal_schd_trip.cal_schd_trip_id = cm.cal_schd_trip_id
left outer join trlr t
on t.trlr_id = cm.trlr_id
left outer join carhdr c
on c.carcod = cm.carcod
left outer join adrmst ca
on ca.adr_id = c.adr_id
left outer join cstmst sfc
on ( sfc.cstnum = o.sfcust
and sfc.client_id = o.client_id)
left outer join cstmst stc
on ( stc.cstnum = o.stcust
and stc.client_id = o.client_id)
left outer join cstmst btc
on ( btc.cstnum = o.btcust
and btc.client_id = o.client_id)
left outer join cstmst rtc
on ( rtc.cstnum = o.rtcust
and rtc.client_id = o.client_id)
left outer join prtmst prt
on ( prt.prtnum = ol.prtnum
and prt.prt_client_id = ol.client_id
and prt.wh_id_tmpl = ol.wh_id)
Re: ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table [message #340820 is a reply to message #340814] Thu, 14 August 2008 08:47 Go to previous message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above
Previous Topic: nested decode help
Next Topic: Create Table As Select
Goto Forum:
  


Current Time: Sun Dec 04 14:53:03 CST 2016

Total time taken to generate the page: 0.15180 seconds