Re: Is this possible in oracle

From: <nekkalapudi.siva_at_gmail.com>
Date: 27 Mar 2005 21:05:16 -0800
Message-ID: <1111986316.751841.253480@f14g2000cwb.googlegroups.com>

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. Received on Sun Mar 27 2005 - 23:05:16 CST