Getting errored while creating the Materialised View..

[salapati]

Team,

I am trying to create the Materialised View and getting the following error message.

ORA-22818: subquery expressions not allowed here.

If i try to create the View, its working fine and the SELECT statement also working fine.

QUERY:
*******

CREATE MATERIALIZED VIEW XRX_OBI.MXROBI_ORG_SUBINV_SETUP_MV
TABLESPACE XRXOBID
NOCACHE
NOLOGGING
NOPARALLEL
REFRESH COMPLETE ON DEMAND AS
SELECT
DISTINCT mp.organization_id,mp.organization_code,
DECODE (mp.general_ledger_update_code,
1, 'Detail',
2, 'Summary',
3, 'None'
) "Transfer to GL",
mat_acc.segment1
|| '-'
|| mat_acc.segment2
|| '-'
|| mat_acc.segment3 "Material Account",
out_val_acc.segment1||'-'|| out_val_acc.segment2||'-'|| out_val_acc.segment3 "Outside Valuation Account",
mat_over_acc.segment1||'-'||mat_over_acc.segment2||'-'|| mat_over_acc.segment3 "Material Overhead Account",
over_acc.segment1
|| '-'
|| over_acc.segment2
|| '-'
|| over_acc.segment3 "Overhead Account",
res_acc.segment1
|| '-'
|| res_acc.segment2
|| '-'
|| res_acc.segment3 "Resource Account",
exp_acc.segment1
|| '-'
|| exp_acc.segment2
|| '-'
|| exp_acc.segment3 "Expense Account",
inter_trans_cr_acc.segment1
|| '-'
|| inter_trans_cr_acc.segment2
|| '-'
|| inter_trans_cr_acc.segment3 "Interorg Trans CR Account",
inter_price_var_acc.segment1
|| '-'
|| inter_price_var_acc.segment2
|| '-'
|| inter_price_var_acc.segment3 "Interorg Price VARAccount",
inter_rec_acc.segment1
|| '-'
|| inter_rec_acc.segment2
|| '-'
|| inter_rec_acc.segment3 "Interorg Rec Account",
inter_pay_acc.segment1
|| '-'
|| inter_pay_acc.segment2
|| '-'
|| inter_pay_acc.segment3 "Interorg Pay Account",
inter_inv_acc.segment1
|| '-'
|| inter_inv_acc.segment2
|| '-'
|| inter_inv_acc.segment3 "Intransit Inv Account",
pur_pri_var_acc.segment1
|| '-'
|| pur_pri_var_acc.segment2
|| '-'
|| pur_pri_var_acc.segment3 "Purchase Price VAR Account",
inv_price_var_acc.segment1
|| '-'
|| inv_price_var_acc.segment2
|| '-'
|| inv_price_var_acc.segment3 "Invoice Price VAR Account",
ap_accural_acc.segment1
|| '-'
|| ap_accural_acc.segment2
|| '-'
|| ap_accural_acc.segment3 "Inventory AP Accural Account",
encum_acc.segment1
|| '-'
|| encum_acc.segment2
|| '-'
|| encum_acc.segment3 "Encumbrance Account",
sales_acc.segment1
|| '-'
|| sales_acc.segment2
|| '-'
|| sales_acc.segment3 "Sales Account",
cost_of_sales_acc.segment1
|| '-'
|| cost_of_sales_acc.segment2
|| '-'
|| cost_of_sales_acc.segment3 "Cost OF Goods Sold Account",
rp.qty_rcv_tolerance||'%' "Tolerance %",
rp.qty_rcv_exception_code "Tolerance Action",
rec_inv_acc.segment1
|| '-'
|| rec_inv_acc.segment2
|| '-'
|| rec_inv_acc.segment3 "Receiving INV Account",
rrh.routing_name "Receipt Routing",
rrh1.routing_name "RMA Receipt Routing",
rp.days_early_receipt_allowed "Days Early",
rp.days_late_receipt_allowed "Days Late",
rp.receipt_days_exception_code "Receipt Action",
subinv.secondary_inventory_name "Subinventory Name",
subinv.description "Subinventory Desc",
DECODE (subinv.quantity_tracked,
2, 'Not Checked',
1, 'Checked'
) "Qunatity Tracked",
DECODE (subinv.asset_inventory,
2, 'Not Checked',
1, 'Checked'
) "Asset Subinventory",
DECODE (subinv.depreciable_flag,
2, 'Not Checked',
1, 'Checked'
) "Depreciable Flag",
DECODE (subinv.inventory_atp_code,
2, 'Not Checked',
1, 'Checked'
) "Include in ATP",
DECODE (subinv.reservable_type,
2, 'Not Checked',
1, 'Checked'
) "Reservation Type",
--Need to check Nettable once
DECODE (subinv.availability_type,
2, 'Not Checked',
1, 'Checked'
) "Nettable",
/*DECODE (subinv.lpn_controlled_flag,
2, 'Not Checked',
1, 'Checked'
) "LPN Controlled", */
DECODE (subinv.cartonization_flag,
2, 'Not Checked',
1, 'Checked'
) "Enable Cartonization",
subinv.disable_date "Inactive On",
--need to check the below once
-- subinv.availability_type,
DECODE (subinv.locator_type,
1, 'None',
2, 'Prespecified',
3, 'Dynamic Entry',
4, 'Item level'
) "SUBINV Locator Control",
DECODE (subinv.source_type,
1, 'Inventory',
2, 'Supplier',
3, 'Subinventory'
) "SUBINV Sourcing Type",
subinv.source_subinventory "SUBINV Sourcing Subinventory",
subinv_mat_acc.segment1
|| '-'
|| subinv_mat_acc.segment2
|| '-'
|| subinv_mat_acc.segment3 "SUBINV Material Account",
subinv_out_val_acc.segment1
|| '-'
|| subinv_out_val_acc.segment2
|| '-'
|| subinv_out_val_acc.segment3 "SUBINV Outside Val Account",
subinv_mat_over_acc.segment1
|| '-'
|| subinv_mat_over_acc.segment2
|| '-'
|| subinv_mat_over_acc.segment3
"SUBINV Mat Overhead Account",
subinv_over_acc.segment1
|| '-'
|| subinv_over_acc.segment2
|| '-'
|| subinv_over_acc.segment3 "SUBINV Overhead Account",
subinv_res_acc.segment1
|| '-'
|| subinv_res_acc.segment2
|| '-'
|| subinv_res_acc.segment3 "SUBINV Resource Account",
subinv_exp_acc.segment1
|| '-'
|| subinv_exp_acc.segment2
|| '-'
|| subinv_exp_acc.segment3 "SUBINV Expense Account",
subinv_encum_acc.segment1
|| '-'
|| subinv_encum_acc.segment2
|| '-'
|| subinv_encum_acc.segment3 "SUBINV Encumbrance Account",
(select tmsm.budget_center
from apps.TXRMS0_MTL_SUBINVENTORIES_MP tmsm
where tmsm.organization_id=mp.organization_id
and tmsm.SECONDARY_INVENTORY_NAME=subinv.secondary_inventory_name) budget_center,
issue_subinventory "Issue Subinventory",
receipt_subinventory "Receipt Subinventory",
offset_trans_cr_acc.segment1
|| '-'
|| offset_trans_cr_acc.segment2
|| '-'
|| offset_trans_cr_acc.segment3 "Offset trans cr amount",
tl.location_code Name, tl.description, l.address_line_1,
l.address_line_2, l.address_line_3, l.town_or_city,
l.region_1, l.region_2, l.postal_code, l.country, l.region_3,
l.telephone_number_1, l.telephone_number_2,
l.telephone_number_3, tl.location_code Ship_to_location, l.ship_to_site_flag,
l.bill_to_site_flag, l.receiving_site_flag,
l.in_organization_flag, l.office_site_flag,
ood.organization_code
|| '-'
|| ood.organization_name "Inventory Organization",
o.NAME, o.TYPE, o.date_from, o.date_to, hl.meaning,
loc.location_code,
/* loc.address_line_1, loc.address_line_2,
loc.address_line_3, loc.town_or_city, loc.region_1,
loc.region_2, loc.postal_code, loc.country,
loc.telephone_number_1, loc.telephone_number_2,
loc.telephone_number_3,*/
inventory_enabled_flag,
sb.short_name
|| '|'
|| le.NAME
|| '|'
|| hu.NAME "Accounting Information",
cust_info.customer|| '|' ||cust_site_info.address || '|' ||supp_info.vendor_name||'|'|| '|'||supp_site_info.vendor_site_code "Customer/Supplier Asscoiation",
masorg.NAME master_org,
mp.calendar_code,
DECODE
(mp.mo_approval_timeout_action,
1, 'Approve automatically',
2, 'Reject automatically'
) "Move Order Timeout Action",
DECODE
(mp.stock_locator_control_code,
1, 'None',
2, 'Prespecified Only',
3, 'Dynamic Entry allowed',
4, 'Determined at subiniventory level'
) "Locator Control",
DECODE
(mp.negative_inv_receipt_code,
1, 'Checked',
2, 'Not Checked'
) "Allow Negative Balances",
mp.starting_revision,
DECODE (mp.lot_number_uniqueness,
1, 'Across items',
2, 'None'
) "Lot Control Uniqueness",
DECODE (mp.lot_number_generation,
1, 'At organization level',
2, 'At item level',
3, 'User defined'
) "Lot Control Generation",
DECODE (mp.lot_number_zero_padding,
1, 'Checked',
2, 'Not Checked'
) "Zero Pad Suffix",
mp.auto_lot_alpha_prefix "Lot Control Prefix",
mp.lot_number_length "Lot Control Total Length",
DECODE
(mp.serial_number_type,
1, 'Within inventory model and items',
2, 'Within organization',
3, 'Across organizations',
4, 'Within inventory items'
) "Serial Control Uniqueness",
DECODE
(mp.serial_number_generation,
1, 'At orgnanization level',
2, 'At item level',
3, 'User defined'
) "Serial Control Generation",
mp.auto_serial_alpha_prefix "Serial Control Prefix",
mp.start_auto_serial_number "Starting Serial Number",
DECODE (mp.allocate_serial_flag,
'N', 'No',
'Y', 'Yes',
'C', 'Yes-User Defined'
) "Allocate Serial Numbers"
FROM apps.mtl_parameters mp,
apps.gl_code_combinations mat_acc,
apps.gl_code_combinations out_val_acc,
apps.gl_code_combinations mat_over_acc,
apps.gl_code_combinations over_acc,
apps.gl_code_combinations res_acc,
apps.gl_code_combinations exp_acc,
apps.gl_code_combinations inter_trans_cr_acc,
apps.gl_code_combinations inter_price_var_acc,
apps.gl_code_combinations inter_rec_acc,
apps.gl_code_combinations inter_pay_acc,
apps.gl_code_combinations inter_inv_acc,
apps.gl_code_combinations pur_pri_var_acc,
apps.gl_code_combinations inv_price_var_acc,
apps.gl_code_combinations ap_accural_acc,
apps.gl_code_combinations encum_acc,
apps.gl_code_combinations sales_acc,
apps.gl_code_combinations cost_of_sales_acc,
apps.rcv_parameters rp,
apps.rcv_routing_headers rrh,
apps.rcv_routing_headers rrh1,
apps.gl_code_combinations rec_inv_acc,
apps.mtl_secondary_inventories subinv,
apps.gl_code_combinations subinv_mat_acc,
apps.gl_code_combinations subinv_out_val_acc,
apps.gl_code_combinations subinv_mat_over_acc,
apps.gl_code_combinations subinv_over_acc,
apps.gl_code_combinations subinv_res_acc,
apps.gl_code_combinations subinv_exp_acc,
apps.gl_code_combinations subinv_encum_acc,
xrx.txrms0_mtl_subinventories_mp tmtlsubinv,
apps.txrcl0_customer_location_mp clm,
apps.gl_code_combinations offset_trans_cr_acc,
hr_locations_no_join loc,
apps.hr_organization_units o,
apps.hr_locations_all l,
apps.hr_locations_all_tl tl,
apps.org_organization_definitions ood,
hr_lookups hl,
apps.hr_organization_information i,
apps.hr_operating_units hu,
apps.gl_sets_of_books sb,
apps.hr_legal_entities le,
apps.hr_organization_units masorg,
(SELECT i.organization_id,
SUBSTRB (party.party_name, 1, 50)
|| ' ('
|| cust.account_number
|| ')' customer
FROM apps.hz_cust_accounts cust,
apps.hz_parties party,
apps.hr_organization_information i
WHERE cust.party_id = party.party_id
AND TO_CHAR (cust.cust_account_id) = i.org_information1
AND i.org_information_context =
'Customer/Supplier Association'
ORDER BY party.party_name,
cust.account_number,
cust.cust_account_id) cust_info,
(SELECT i.organization_id, address
FROM apps.mrp_customer_sites_v,
apps.hr_organization_information i
WHERE TO_CHAR (site_use_id) = i.org_information2
and I.ORG_INFORMATION2 IS NOT NULL
AND i.org_information_context =
'Customer/Supplier Association') cust_site_info,
(SELECT i.organization_id, vendor_name
FROM apps.po_vendors pv,
apps.hr_organization_information i
WHERE TO_CHAR (pv.vendor_id) = i.org_information3
AND i.org_information_context =
'Customer/Supplier Association'
ORDER BY vendor_name) supp_info,
(SELECT i.organization_id, vendor_site_code
FROM apps.po_vendor_sites_all,
apps.hr_organization_information i
WHERE TO_CHAR (vendor_site_id) = i.org_information4
and I.ORG_INFORMATION4 IS NOT NULL
AND i.org_information_context =
'Customer/Supplier Association') supp_site_info
WHERE mat_acc.code_combination_id(+) = mp.material_account
AND out_val_acc.code_combination_id(+) =
mp.outside_processing_account
AND mat_over_acc.code_combination_id(+) =
mp.material_overhead_account
AND over_acc.code_combination_id(+) = mp.overhead_account
AND res_acc.code_combination_id(+) = mp.resource_account
AND exp_acc.code_combination_id(+) = mp.expense_account
AND inter_trans_cr_acc.code_combination_id(+) =
mp.interorg_transfer_cr_account
AND inter_price_var_acc.code_combination_id(+) =
mp.interorg_price_var_account
AND inter_rec_acc.code_combination_id(+) =
mp.interorg_receivables_account
AND inter_pay_acc.code_combination_id(+) =
mp.interorg_payables_account
AND inter_inv_acc.code_combination_id(+) =
mp.intransit_inv_account
AND pur_pri_var_acc.code_combination_id(+) =
mp.purchase_price_var_account
AND inv_price_var_acc.code_combination_id(+) =
mp.invoice_price_var_account
AND ap_accural_acc.code_combination_id(+) = mp.ap_accrual_account
AND encum_acc.code_combination_id(+) = mp.encumbrance_account
AND sales_acc.code_combination_id(+) = mp.sales_account
AND cost_of_sales_acc.code_combination_id(+) =
mp.cost_of_sales_account
AND rp.receiving_routing_id = rrh.routing_header_id(+)
AND rp.rma_receipt_routing_id = rrh1.routing_header_id(+)
AND rec_inv_acc.code_combination_id = rp.receiving_account_id
AND mp.organization_id = rp.organization_id
AND subinv.organization_id(+) = mp.organization_id
AND subinv_mat_acc.code_combination_id(+) =
subinv.material_account
AND subinv_out_val_acc.code_combination_id(+) =
subinv.outside_processing_account
AND subinv_mat_over_acc.code_combination_id(+) =
subinv.material_overhead_account
AND subinv_over_acc.code_combination_id(+) =
subinv.overhead_account
AND subinv_res_acc.code_combination_id(+) =
subinv.resource_account
AND subinv_exp_acc.code_combination_id(+) = subinv.expense_account
AND subinv_encum_acc.code_combination_id(+) =
subinv.encumbrance_account
AND offset_trans_cr_acc.code_combination_id(+) =
clm.code_combination_id
AND o.organization_id = tmtlsubinv.organization_id(+)
AND o.location_id = loc.location_id
AND loc.location_code = clm.location_code(+)
AND l.location_id = tl.location_id
AND o.location_id = l.location_id
AND mp.organization_id = ood.organization_id
AND o.organization_id = mp.organization_id
AND o.internal_external_flag = hl.lookup_code(+)
AND hl.lookup_type(+) = 'INTL_EXTL'
AND o.location_id = loc.location_id(+)
AND o.organization_id = i.organization_id
AND ood.operating_unit = hu.organization_id
AND ood.set_of_books_id = sb.set_of_books_id
AND ood.legal_entity = le.organization_id
AND masorg.organization_id = mp.master_organization_id
--AND mp.organization_id = 473
AND cust_info.organization_id(+) = mp.organization_id
AND cust_site_info.organization_id(+) = mp.organization_id
AND supp_info.organization_id(+) = mp.organization_id
AND supp_site_info.organization_id(+) = mp.organization_id;


*********************************************************

Request you all to please help me in this regard.

Thanks in advance,

 

[SantaMufasa]

Salapati,

I'm guessing that we'll be able to help you better if you can post the very smallest, cut-down version of this materialised view that re-creates this error.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Dagon]

It might be the order by ...(select ...) bit. It seems to work on my database (9.2.0.4), but you might have a slightly different version that doesn't support that.

 

[Dagon]

No, I misread that. There is a bracket at the end of the order by, so it isn't order by (select..). Syntactically, the query seems to work on my system. I would first try it as a straight query rather than a materialized view and see if you still get the same error.

 

[SantaMufasa]

It seems to work on my database...

This was my experience (my abbreviated test with a subquery in the MV worked for me). That is why I'm hoping you (Salapati) can trim down your MV creation to the bare minimum example that still generates the error.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Dagon]

The one thing that does seem odd is putting order bys on inline selects. The inline select can only logically return one row, so I can't see much point in ordering the result set.

 

[salapati]

I am getting the error when i was trying to create the materialised view and not for creation of View..

 

[Beilstwh]

Can you make the view and then create the MVIEW using the view instead of using the entire select?

Bill
Oracle DBA/Developer
New York State, USA

 

[Beilstwh]

Did using the view as a first step work for you MVIEW or is this still an issue?

Bill
Oracle DBA/Developer
New York State, USA