Hi guys
I was wondering if someone might be able to shed some light on this.
I have taken an export of our apps development database, and imported it onto my own machine.
There are a number of views that will not compile on my database, but are compiled fine on the development box.
So I know the SQL is fine.
My version of the database is 11.2.0.1.0, and the one I exported it from is 11.1.0.7.0 so there is a slight difference there, so I guess that could be the issue.
So the message I get is :
ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table
And an example piece of sql causing this follows. Can anyone suggest why these views would be fine on one machine, but not on an exported version of the same database on my machine ?
CREATE OR REPLACE VIEW OE_TRANSACTION_TYPES_V
(row_id, transaction_type_id, transaction_type_code, order_category_code, start_date_active, end_date_active, creation_date, created_by, last_update_date, last_updated_by, last_update_login, program_application_id, program_id, request_id, currency_code, conversion_type_code, cust_trx_type_id, cost_of_goods_sold_account, entry_credit_check_rule_id, shipping_credit_check_rule_id, price_list_id, enforce_line_prices_flag, min_margin_percent, warehouse_id, demand_class_code, shipment_priority_code, shipping_method_code, freight_terms_code, fob_point_code, ship_source_type_code, agreement_type_code, agreement_required_flag, po_required_flag, invoicing_rule_id, invoicing_credit_method_code, accounting_rule_id, accounting_credit_method_code, invoice_source_id, non_delivery_invoice_source_id, default_inbound_line_type_id, default_outbound_line_type_id, inspection_required_flag, depot_repair_code, auto_scheduling_flag, scheduling_level_code, default_fulfillment_set, default_line_set_code, context, attribute1, attribute2, attribute3, attribute4, attribute5, attribute6, attribute7, attribute8, attribute9, attribute10, attribute11, attribute12, attribute13, attribute14, attribute15, name, description, org_id, organization_name, tax_calculation_event_code, picking_credit_check_rule_id, packing_credit_check_rule_id, sales_document_type_code, sales_document_type, def_transaction_phase_code, quote_num_as_ord_num_flag, layout_template_id, contract_template_id)
AS
SELECT vl.ROWID ROW_ID, vl.TRANSACTION_TYPE_ID, vl.TRANSACTION_TYPE_CODE, vl.ORDER_CATEGORY_CODE, vl.START_DATE_ACTIVE, vl.END_DATE_ACTIVE, vl.CREATION_DATE, vl.CREATED_BY, vl.LAST_UPDATE_DATE, vl.LAST_UPDATED_BY, vl.LAST_UPDATE_LOGIN, vl.PROGRAM_APPLICATION_ID, vl.PROGRAM_ID, vl.REQUEST_ID, vl.CURRENCY_CODE, vl.CONVERSION_TYPE_CODE, vl.CUST_TRX_TYPE_ID, vl.COST_OF_GOODS_SOLD_ACCOUNT, vl.ENTRY_CREDIT_CHECK_RULE_ID, vl.SHIPPING_CREDIT_CHECK_RULE_ID, vl.PRICE_LIST_ID, vl.ENFORCE_LINE_PRICES_FLAG, vl.MIN_MARGIN_PERCENT, vl.WAREHOUSE_ID, vl.DEMAND_CLASS_CODE, vl.SHIPMENT_PRIORITY_CODE, vl.SHIPPING_METHOD_CODE, vl.FREIGHT_TERMS_CODE, vl.FOB_POINT_CODE, vl.SHIP_SOURCE_TYPE_CODE, vl.AGREEMENT_TYPE_CODE, vl.AGREEMENT_REQUIRED_FLAG, vl.PO_REQUIRED_FLAG, vl.INVOICING_RULE_ID, vl.INVOICING_CREDIT_METHOD_CODE, vl.ACCOUNTING_RULE_ID, vl.ACCOUNTING_CREDIT_METHOD_CODE, vl.INVOICE_SOURCE_ID, vl.NON_DELIVERY_INVOICE_SOURCE_ID, vl.DEFAULT_INBOUND_LINE_TYPE_ID, vl.DEFAULT_OUTBOUND_LINE_TYPE_ID, vl.INSPECTION_REQUIRED_FLAG, vl.DEPOT_REPAIR_CODE, vl.AUTO_SCHEDULING_FLAG, vl.SCHEDULING_LEVEL_CODE, vl.DEFAULT_FULFILLMENT_SET, vl.DEFAULT_LINE_SET_CODE, vl.CONTEXT, vl.ATTRIBUTE1, vl.ATTRIBUTE2, vl.ATTRIBUTE3, vl.ATTRIBUTE4, vl.ATTRIBUTE5, vl.ATTRIBUTE6, vl.ATTRIBUTE7, vl.ATTRIBUTE8, vl.ATTRIBUTE9, vl.ATTRIBUTE10, vl.ATTRIBUTE11, vl.ATTRIBUTE12, vl.ATTRIBUTE13, vl.ATTRIBUTE14, vl.ATTRIBUTE15, vl.NAME, vl.DESCRIPTION, vl.ORG_ID, hou.NAME, vl.TAX_CALCULATION_EVENT_CODE, vl.PICKING_CREDIT_CHECK_RULE_ID, vl.PACKING_CREDIT_CHECK_RULE_ID, vl.SALES_DOCUMENT_TYPE_CODE, oel.meaning SALES_DOCUMENT_TYPE, vl.def_transaction_phase_code, vl.quote_num_as_ord_num_flag, vl.layout_template_id, vl.contract_template_id
FROM OE_TRANSACTION_TYPES_VL vl, HR_ORGANIZATION_UNITS hou, oe_lookups oel
WHERE vl.org_id = hou.organization_id(+) and oel.lookup_code (+) = vl.sales_document_type_code and oel.lookup_type (+) = 'SALES_DOCUMENT_TYPE'
Thanks very much