Re: Is this possible in oracle

nekkalapudi.siva_at_gmail.com wrote:

> The question is I have a view which looks like
>
> SELECT pai.action_context_id assignment_action_id
> , pai.action_information1 org_paymeth_id
> , pop.org_payment_method_id org_payment_method_id
> , pop.org_payment_method_name org_payment_method_name
> , ppto.category org_payment_category
> , pea.segment1 org_bank_name
> , pea.segment2 org_bank_branch
> , pea.segment3 org_bank_account_number
> , pai.action_information2 personal_payment_method_id
> , pai.action_information18 personal_payment_method_name
> , pptp.category personal_payment_category
> , pai.action_information5 personal_bank_name
> , pai.action_information6 personal_bank_branch
> , pai.action_information7 personal_bank_account_number
> , pai.action_information13 currency_code
> , pai.action_information16 payment_amount
> FROM pay_action_information pai
> , pay_personal_payment_methods_f ppp
> , pay_org_payment_methods_f popp
> , pay_payment_types pptp
> , pay_org_payment_methods_f pop
> , pay_payment_types ppto
> , pay_external_accounts pea
> WHERE pai.action_information_category = 'EMPLOYEE NET PAY DISTRIBUTION'
> AND pai.action_context_type = 'AAP'
> AND pai.action_information2 = ppp.personal_payment_method_id
> AND pai.effective_date BETWEEN ppp.effective_start_date
> AND ppp.effective_end_date
> AND ppp.org_payment_method_id = popp.org_payment_method_id
> AND pai.effective_date BETWEEN popp.effective_start_date
> AND popp.effective_end_date
> AND popp.payment_type_id = pptp.payment_type_id
> AND pai.action_information1 = pop.org_payment_method_id
> AND pai.effective_date BETWEEN pop.effective_start_date
> AND pop.effective_end_date
> AND pop.payment_type_id = ppto.payment_type_id
> AND pop.external_account_id = pea.external_account_id
> UNION ALL
> SELECT pai.action_context_id assignment_action_id
> , pai.action_information1 org_paymeth_id
> , pop.org_payment_method_id org_payment_method_id
> , pai.action_information18 org_payment_method_name
> , ppt.category org_payment_category
> , pai.action_information5 org_bank_name
> , pai.action_information6 org_bank_branch
> , pai.action_information7 org_bank_account_number
> , pai.action_information1 personal_payment_method_id
> , pai.action_information18 personal_payment_method_name
> , ppt.category personal_payment_category
> , pai.action_information5 personal_bank_name
> , pai.action_information6 personal_bank_branch
> , pai.action_information7 personal_bank_account_number
> , pai.action_information13 currency_code
> , pai.action_information16 payment_amount
> FROM pay_action_information pai
> , pay_org_payment_methods_f pop
> , pay_payment_types ppt
> WHERE pai.action_information_category = 'EMPLOYEE NET PAY DISTRIBUTION'
> AND pai.action_context_type = 'AAP'
> AND pai.action_information2 IS NULL
> AND pai.action_information1 = pop.org_payment_method_id
> AND pop.payment_type_id = ppt.payment_type_id
> AND pai.effective_date BETWEEN pop.effective_start_date
> AND pop.effective_end_date
>
> Now the problem is its a non-mergeable view as it contains union all.
>
> Can we any how make the view mergeable.
>
> I want to remove Union ALL as both the where conditions are quite
> similar and the data(I mean columns) that are picked from the table are
> almost same.

Why not just write an INSTEAD-OF trigger?

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)