ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved

[ramanan ram]

i am using a view query in oracle.if i am run the query successfully executed.but,th e same query applying in asp page(front end)it's showing ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table.

please help...

[debasisdas]

Please post the underlying SQL of the view with structure of all the base tables for further analysis.

[ramanan ram]

select v4.JOBREFNO AS INV, V1.ORIGIN_FV_PRQ,V1.PDFNAME_PRQ,V1.COUNTRY, V1.VESSELNAME_FV_PRQ, V1.VOYAGENO_FV_PRQ, to_char(V1.ETD_FV,'dd-Mon-YYYY') as ETD_FV, to_char(V1.ETA_FV_PRQ,'dd-Mon-YYYY') as ETA_FV_PRQ, V1.ORIGIN_PRQ, V1.SHIPPER_PRQ, V1.BILLTO_PRQ,V1.BILLTOPREPAID_PRQ, V1.POID_PRQ, V1.STYLE_PRQ, V1.MVSNO_PRQ,V1.VESSELNAME_PRQ, V1.VOYAGENO_PRQ, V1.HBL_PRQ, V1.JOBREFNO_PRQ, to_char(V1.JOBDATE_JOB,'dd-Mon-YYYY') as JOBDATE_JOB, V1.SUPPLIERINVNO_PRQ, V1.SUPPLIERINVNO_PRQ, V1.POCONTAINER_PRQ, V1.CSIZE_PRQ, V1.SEAL_PRQ, to_char(V1.ETD_PRQ,'dd-Mon-YYYY') as ETD_PRQ , to_char(V1.ETA_PRQ,'dd-Mon-YYYY') as ETA_PRQ , V1.POA_PRQ, V1.CCTNS_PRQ,V1.NOP_PRQ, V1.QTY_PRQ, V1.CONSIGNEENAME_PRQ, V1.POSTYLEGW_PRQ, V1.POSTYLECUBE_PRQ, V1.POA_FV_PRQ,V1.CSTATUS,v2.COLLFROM,v2.PORT,v2.DEL_ADDRESS,v2.BOOKEDBY,to_char(v2.BOOKEDON,'dd-Mon-YYYY') as BOOKEDON,v2.CONSIGNEEBOOKINGREF,to_char(v2.CONSIGNEEBOOKINGDATE,'dd-Mon-YYYY') as CONSIGNEEBOOKINGDATE,v2.BOOKEDBY,to_char(v2.DELIVERYDATE,'dd-Mon-YYYY') as DELIVERYDATE ,v2.PODSIGNEDBY,v2.REMARKS from ETRANS3103.SEA_IMPORTS_PRQJOB_NEW@etrans1_link v1 left join etrans3103.SEA_GENERIC_DELIVERY_VIEW_BD@etrans1_link v2 on V1.JOBREFNO_PRQ=v2.JOBREFNO and V1.POCONTAINER_PRQ=v2.CONTAINERNO LEFT JOIN etrans3103.INVOICE_CHECK_SEA@etrans1_link v4 on V1.JOBREFNO_PRQ=v4.JOBREFNO where (CONSIGNEEID_PRQ in ('LONSTAT8354','LONGE H11979','LONSTAT8549','LONGE H13089','LONGE H14470','LONGE H18495','LONGENO6219','LONGELI5022','LONGE L10465','LONGE L10464','LONGE N1530','LONGE H40459' ) or BILLTOPREPAID_PRQ in ('GE HUNGARY LIMITED','GE HUNGARY. RT.','GE HUNGARY. RT.','GE LIGHTING LIMITED - LEICESTER','GE LIGHTING LIMITED- ENFIELD','GE LIGHTING LTD - NORTHAMPTON','GE LIGHTING OPERATIONS LIMITED - NORTHAMPTON','GE LIGHTING SALES AND MKTG LEICS','GE HUNGARY LIMITED','GE LIGHTING LIMITED- ENFIELD') or BILLTOCOLLECT_PRQ in ('GE HUNGARY LIMITED','GE HUNGARY. RT.','GE HUNGARY. RT.','GE LIGHTING LIMITED - LEICESTER','GE LIGHTING LIMITED- ENFIELD','GE LIGHTING LTD - NORTHAMPTON','GE LIGHTING OPERATIONS LIMITED - NORTHAMPTON','GE LIGHTING SALES AND MKTG LEICS','GE HUNGARY LIMITED','GE LIGHTING LIMITED- ENFIELD')) and (V1.ETA_PRQ between '01-Jul-2011' and '02-Aug-2012') order by ETA_PRQ DESC , v1.HBL_PRQ asc, v1.QTY_PRQ desc

here,inside of query using more view..
this query executed in toad ,but front end page(asp) showing error message.

[debasisdas]

Try to create a materialized view with BUILD IMMEDIATE by using the Underlying SQL.