Conversion Approach [message #653184] |
Tue, 28 June 2016 12:07 |
|
Good day good people.
I have this block of code, needed to convert to Oracle from SQL.
Declare @Rundate date = (Select a.RunDate from dbo.vw_cor_solutionrundate a where a.Alias = 'MPR');
--Account Table mis update
Update mpr_balancesheet
set TeamCode = a.TeamCode,
AccountOfficerCode = a.AccountOfficerCode
from dbo.cor_cust_account a where a.AccountNo = mpr_balancesheet.AccountNo;
--Fix Team code of Account Officers
Update mpr_balancesheet
set TeamCode = a.ParentCode
from mpr_team a where a.Year = Year(@Rundate) and
a.Code = mpr_balancesheet.AccountOfficerCode
and a.CompanyCode = mpr_balancesheet.companycode;
--Product mis update
Update mpr_balancesheet
set TeamCode = a.TeamCode,
AccountOfficerCode = a.accountofficercode
from dbo.vw_mpr_productmis a where a.ProductCode = mpr_balancesheet.ProductCode
and a.CaptionName = mpr_balancesheet.CaptionName;
--Branch Update
Update mpr_balancesheet
set TeamCode = a.MisCode,
AccountOfficerCode = 'n/a'
from dbo.mpr_branchdefaultmis a where a.BranchCode = mpr_balancesheet.BranchCode
and mpr_balancesheet.VolumeGL in (select b.ProductCode from mpr_product b)
and a.Deleted = 0 and a.Active = 1;
--Fintrak Account MIS
Update mpr_balancesheet
set TeamCode = a.TeamCode,
AccountOfficerCode = a.AccountOfficerCode
from dbo.mpr_accountmis a where
a.accountno = mpr_balancesheet.AccountNo and a.Deleted = 0 and a.Active = 1;
Kindly advice, to use
1. To convert 'as is' to Oracle
2. Merge statement
3. Cursor Approach
4. Any suggestion/Approach.
Please note: Performance on my mind. mpr_balancesheet is a big table with 12 Months x 7millions Accounts.
Many thanks.
|
|
|
Re: Conversion Approach [message #653186 is a reply to message #653184] |
Tue, 28 June 2016 12:20 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
I will give you the first 2 updates to show you the form. I don't feel like altering all the issues
declare
rundate date;
begin
Select a.RunDate into rundate from vw_cor_solutionrundate a where a.Alias = 'MPR';
--Account Table mis update
update mpr_balancesheet a
set (TeamCode,AccountOfficerCode) =
(select b.TeamCode,b.AccountOfficerCode
from cor_cust_account b
where b.AccountNo = a.AccountNo)
where exists
(select null
from cor_cust_account b
where b.AccountNo = a.AccountNo);
--Fix Team code of Account Officers
Update mpr_balancesheet a
set a.TeamCode =
(select b.ParentCode
from mpr_team b
where b.Year = to_number(to_char(Rundate,'YYYY'))
and B.Code = A.AccountOfficerCode
and B.CompanyCode = A.companycode)
WHERE EXISTS
(select NULL
from mpr_team b
where b.Year = to_number(to_char(Rundate,'YYYY'))
and B.Code = A.AccountOfficerCode
and B.CompanyCode = A.companycode);
end;
/
|
|
|
|
|
|