I have a set of ETL jobs that are loading data from an MS SQL database to an Oracle. The jobs and parameters are defined in tables and there's a wrapper procedure that executes them one by one.
My problem is that when the wrapper executes the first job, it's fast, inserts the data into Oracle in about 45-50 seconds. When it gets to the second one, which only differs from the first in taking different exchange rates from the source, it takes 20-22 minutes.
Exactly the same number of rows (about 24 thousand), same amount of data, same source and destination tables, the only difference is in the where clause of the source query.
I have implemented a quite extensive logging and it shows that loading, transforming and pivoting the data from the source database has a steady performance, always taking roughly the same amount of time. It's only the last step, pushing to oracle that is fast for the first time, sluggish for the rest.
Provider used: OraOLEDB.Oracle
Insert statement used:
insert into LINKEDORACLESERVER..SCHEMANAME.TABLENAME select * from spec.staging_tablename
Any ideas what to check?
UPDATE:
Got an idea from user ammoQ in the comments. The thing is that if I reverse the two jobs then the first one becomes the painfully slow one and the 2nd is fast. Probably the whole question is wrong now.
But how can this happen if the two jobs are only different in their parameters, specifically the exchange rate categories they should take from SAP BPC's OLAP cube? They are reading from the same source, writing to the same destination table, both about 24k rows but with "Rate B" instead of "Rate A".
UPDATE 2: Getting closer a bit. So both jobs are pushing the data into a table that's meant to be a data mart for an Xcelsius dashboard. It has a pretty special structure for which I built a view in MS SQL. So loading data from the same view to the same table.
View in job 1: returns 23,512 rows. Execution time: 00:01:03.
View in job 2: returns 23,512 rows. Execution time: 00:18:54.
This is the view's select:
select base.organisation, calc.mm_account, base.analysis,
case base.kpiref when -1 then calc.source_of_value else sv1.value end source_of_value,
base.account, base.[year], 'ACTUAL' category,
[currency] currency_code, ltrim(rtrim(right(jobs.name, len(jobs.name) - patindex('%@%', jobs.name)))) forex_rate_type, base.[month],
isnull(Y_0.v01, 0) v01,
case base.[month] when 1 then 0 else isnull(Y_0.v02, 0) end v02,
case base.[month] when 2 then 0 else isnull(Y_0.v03, 0) end v03,
case base.[month] when 3 then 0 else isnull(Y_0.v04, 0) end v04,
case base.[month] when 4 then 0 else isnull(Y_0.v05, 0) end v05,
case base.[month] when 5 then 0 else isnull(Y_0.v06, 0) end v06,
case base.[month] when 6 then 0 else isnull(Y_0.v07, 0) end v07,
case base.[month] when 7 then 0 else isnull(Y_0.v08, 0) end v08,
case base.[month] when 8 then 0 else isnull(Y_0.v09, 0) end v09,
case base.[month] when 9 then 0 else isnull(Y_0.v10, 0) end v10,
case base.[month] when 10 then 0 else isnull(Y_0.v11, 0) end v11,
case base.[month] when 11 then 0 else isnull(Y_0.v12, 0) end v12,
isnull(Y_0.CMO, 0) CMO, (0) forecast_accuracy, isnull(Y_0.YTD, 0) YTD, (0) QTD, isnull(Y_0.FYR, 0) FYR,
(isnull(Ym1.v01, 0) + isnull(Ym1.v02, 0) + isnull(Ym1.v03, 0) + isnull(Ym1.v04, 0) + isnull(Ym1.v05, 0) + isnull(Ym1.v06, 0) + isnull(Ym1.v07, 0) + isnull(Ym1.v08, 0) + isnull(Ym1.v09, 0) + isnull(Ym1.v10, 0) + isnull(Ym1.v11, 0) + isnull(Ym1.v12, 0)) FYR_1,
(isnull(Ym2.v01, 0) + isnull(Ym2.v02, 0) + isnull(Ym2.v03, 0) + isnull(Ym2.v04, 0) + isnull(Ym2.v05, 0) + isnull(Ym2.v06, 0) + isnull(Ym2.v07, 0) + isnull(Ym2.v08, 0) + isnull(Ym2.v09, 0) + isnull(Ym2.v10, 0) + isnull(Ym2.v11, 0) + isnull(Ym2.v12, 0)) FYR_2,
0 FYR_NY, -- we don't need to calculate this one for actuals as there are no actuals for next year yet! this is why it's a hard-coded zero
(isnull(Ym2.v01,0)+isnull(Ym2.v02,0)+isnull(Ym2.v03,0)) q01, (isnull(Ym2.v04,0)+isnull(Ym2.v05,0)+isnull(Ym2.v06,0)) q02, (isnull(Ym2.v07,0)+isnull(Ym2.v08,0)+isnull(Ym2.v09,0)) q03, (isnull(Ym2.v10,0)+isnull(Ym2.v11,0)+isnull(Ym2.v12,0)) q04,
(isnull(Ym1.v01,0)+isnull(Ym1.v02,0)+isnull(Ym1.v03,0)) q05, (isnull(Ym1.v04,0)+isnull(Ym1.v05,0)+isnull(Ym1.v06,0)) q06, (isnull(Ym1.v07,0)+isnull(Ym1.v08,0)+isnull(Ym1.v09,0)) q07, (isnull(Ym1.v10,0)+isnull(Ym1.v11,0)+isnull(Ym1.v12,0)) q08,
( isnull(Y_0.v01,0) + case base.[month] when 1 then 0 else isnull(Y_0.v02,0) end + case base.[month] when 2 then 0 else isnull(Y_0.v03,0) end) q09,
(case base.[month] when 3 then 0 else isnull(Y_0.v04,0) end + case base.[month] when 4 then 0 else isnull(Y_0.v05,0) end + case base.[month] when 5 then 0 else isnull(Y_0.v06,0) end) q10,
(case base.[month] when 6 then 0 else isnull(Y_0.v07,0) end + case base.[month] when 7 then 0 else isnull(Y_0.v08,0) end + case base.[month] when 8 then 0 else isnull(Y_0.v09,0) end) q11,
(case base.[month] when 9 then 0 else isnull(Y_0.v10,0) end + case base.[month] when 10 then 0 else isnull(Y_0.v11,0) end + case base.[month] when 11 then 0 else isnull(Y_0.v12,0) end) q12,
dbo.dateOnly(getdate()) date_uploaded, 'strata' data_source
from
(select organisation, account, analysis, [year], kpiref, min([month]) [month], min(jobID) jobID from staging_phase_4
where category in ('ACTUAL', 'ACTUAL-1', 'ACTUAL-2')
and jobID in (select id from dbo.jobs where (name in ('strata @ Actual Average', 'strata @ Plan')))
group by organisation, account, analysis, [year], kpiref) base
left outer join staging_phase_4 Y_0 on Y_0.organisation = base.organisation and Y_0.account = base.account and Y_0.analysis = base.analysis and Y_0.kpiref = base.kpiref and Y_0.category = 'ACTUAL'
left outer join staging_phase_4 Ym1 on Ym1.organisation = base.organisation and Ym1.account = base.account and Ym1.analysis = base.analysis and Ym1.kpiref = base.kpiref and Ym1.category = 'ACTUAL-1'
left outer join staging_phase_4 Ym2 on Ym2.organisation = base.organisation and Ym2.account = base.account and Ym2.analysis = base.analysis and Ym2.kpiref = base.kpiref and Ym2.category = 'ACTUAL-2'
left outer join jobs_calculations_setvalues sv1 on base.kpiref = sv1.calcid
inner join jobs on base.jobID = jobs.id
cross apply spec.calculated_strategic_accounts(base.account) calc
union
-- *** LAST YEAR ***
select base.organisation, calc.mm_account, base.analysis,
case base.kpiref when -1 then calc.source_of_value else sv1.value end source_of_value,
base.account, base.[year], 'LAST YEAR' category,
[currency] currency_code, ltrim(rtrim(right(jobs.name, len(jobs.name) - patindex('%@%', jobs.name)))) forex_rate_type, base.[month],
isnull(Y_0.v01, 0) v01, isnull(Y_0.v02, 0) v02, isnull(Y_0.v03, 0) v03, isnull(Y_0.v04, 0) v04, isnull(Y_0.v05, 0) v05, isnull(Y_0.v06, 0) v06,
isnull(Y_0.v07, 0) v07, isnull(Y_0.v08, 0) v08, isnull(Y_0.v09, 0) v09, isnull(Y_0.v10, 0) v10, isnull(Y_0.v11, 0) v11, isnull(Y_0.v12, 0) v12,
isnull(Y_0.CMO, 0) CMO, (0) forecast_accuracy, isnull(Y_0.YTD, 0) YTD, (0) QTD, isnull(Y_0.FYR, 0) FYR,
(isnull(Ym1.v01, 0) + isnull(Ym1.v02, 0) + isnull(Ym1.v03, 0) + isnull(Ym1.v04, 0) + isnull(Ym1.v05, 0) + isnull(Ym1.v06, 0) + isnull(Ym1.v07, 0) + isnull(Ym1.v08, 0) + isnull(Ym1.v09, 0) + isnull(Ym1.v10, 0) + isnull(Ym1.v11, 0) + isnull(Ym1.v12, 0)) FYR_1,
(isnull(Ym2.v01, 0) + isnull(Ym2.v02, 0) + isnull(Ym2.v03, 0) + isnull(Ym2.v04, 0) + isnull(Ym2.v05, 0) + isnull(Ym2.v06, 0) + isnull(Ym2.v07, 0) + isnull(Ym2.v08, 0) + isnull(Ym2.v09, 0) + isnull(Ym2.v10, 0) + isnull(Ym2.v11, 0) + isnull(Ym2.v12, 0)) FYR_2,
(isnull(Yp1.v01, 0) + isnull(Yp1.v02, 0) + isnull(Yp1.v03, 0) + isnull(Yp1.v04, 0) + isnull(Yp1.v05, 0) + isnull(Yp1.v06, 0) + isnull(Yp1.v07, 0) + isnull(Yp1.v08, 0) + isnull(Yp1.v09, 0) + isnull(Yp1.v10, 0) + isnull(Yp1.v11, 0) + isnull(Yp1.v12, 0)) FYR_NY,
(isnull(Ym2.v01,0)+isnull(Ym2.v02,0)+isnull(Ym2.v03,0)) q01, (isnull(Ym2.v04,0)+isnull(Ym2.v05,0)+isnull(Ym2.v06,0)) q02, (isnull(Ym2.v07,0)+isnull(Ym2.v08,0)+isnull(Ym2.v09,0)) q03, (isnull(Ym2.v10,0)+isnull(Ym2.v11,0)+isnull(Ym2.v12,0)) q04,
(isnull(Ym1.v01,0)+isnull(Ym1.v02,0)+isnull(Ym1.v03,0)) q05, (isnull(Ym1.v04,0)+isnull(Ym1.v05,0)+isnull(Ym1.v06,0)) q06, (isnull(Ym1.v07,0)+isnull(Ym1.v08,0)+isnull(Ym1.v09,0)) q07, (isnull(Ym1.v10,0)+isnull(Ym1.v11,0)+isnull(Ym1.v12,0)) q08,
(isnull(Y_0.v01,0)+isnull(Y_0.v02,0)+isnull(Y_0.v03,0)) q09, (isnull(Y_0.v04,0)+isnull(Y_0.v05,0)+isnull(Y_0.v06,0)) q10, (isnull(Y_0.v07,0)+isnull(Y_0.v08,0)+isnull(Y_0.v09,0)) q11, (isnull(Y_0.v10,0)+isnull(Y_0.v11,0)+isnull(Y_0.v12,0)) q12,
dbo.dateOnly(getdate()) date_uploaded, 'strata' data_source
from
(select organisation, account, analysis, [year], kpiref, min([month]) [month], min(jobID) jobID from staging_phase_4
where category in ('LAST YEAR', 'ACTUAL-2', 'ACTUAL-3', 'ACTUAL')
and jobID in (select id from dbo.jobs where (name in ('strata @ Actual Average', 'strata @ Plan')))
group by organisation, account, analysis, [year], kpiref) base
left outer join staging_phase_4 Y_0 on Y_0.organisation = base.organisation and Y_0.account = base.account and Y_0.analysis = base.analysis and Y_0.kpiref = base.kpiref and Y_0.category = 'LAST YEAR'
left outer join staging_phase_4 Ym1 on Ym1.organisation = base.organisation and Ym1.account = base.account and Ym1.analysis = base.analysis and Ym1.kpiref = base.kpiref and Ym1.category = 'ACTUAL-2'
left outer join staging_phase_4 Ym2 on Ym2.organisation = base.organisation and Ym2.account = base.account and Ym2.analysis = base.analysis and Ym2.kpiref = base.kpiref and Ym2.category = 'ACTUAL-3'
left outer join staging_phase_4 Yp1 on Yp1.organisation = base.organisation and Yp1.account = base.account and Yp1.analysis = base.analysis and Yp1.kpiref = base.kpiref and Yp1.category = 'ACTUAL'
left outer join jobs_calculations_setvalues sv1 on base.kpiref = sv1.calcid
inner join jobs on base.jobID = jobs.id
cross apply spec.calculated_strategic_accounts(base.account) calc
union
-- *** PLAN ***
select base.organisation, calc.mm_account, base.analysis,
case base.kpiref when -1 then calc.source_of_value else sv1.value end source_of_value,
base.account, base.[year], 'PLAN' category,
[currency] currency_code, ltrim(rtrim(right(jobs.name, len(jobs.name) - patindex('%@%', jobs.name)))) forex_rate_type, base.[month],
isnull(Y_0.v01, 0) v01, isnull(Y_0.v02, 0) v02, isnull(Y_0.v03, 0) v03, isnull(Y_0.v04, 0) v04, isnull(Y_0.v05, 0) v05, isnull(Y_0.v06, 0) v06,
isnull(Y_0.v07, 0) v07, isnull(Y_0.v08, 0) v08, isnull(Y_0.v09, 0) v09, isnull(Y_0.v10, 0) v10, isnull(Y_0.v11, 0) v11, isnull(Y_0.v12, 0) v12,
isnull(Y_0.CMO, 0) CMO, (0) forecast_accuracy, isnull(Y_0.YTD, 0) YTD, (0) QTD, isnull(Y_0.FYR, 0) FYR,
(isnull(Am1.v01, 0) + isnull(Am1.v02, 0) + isnull(Am1.v03, 0) + isnull(Am1.v04, 0) + isnull(Am1.v05, 0) + isnull(Am1.v06, 0) + isnull(Am1.v07, 0) + isnull(Am1.v08, 0) + isnull(Am1.v09, 0) + isnull(Am1.v10, 0) + isnull(Am1.v11, 0) + isnull(Am1.v12, 0)) FYR_1,
(isnull(Am2.v01, 0) + isnull(Am2.v02, 0) + isnull(Am2.v03, 0) + isnull(Am2.v04, 0) + isnull(Am2.v05, 0) + isnull(Am2.v06, 0) + isnull(Am2.v07, 0) + isnull(Am2.v08, 0) + isnull(Am2.v09, 0) + isnull(Am2.v10, 0) + isnull(Am2.v11, 0) + isnull(Am2.v12, 0)) FYR_2,
(isnull(Yp1.v01, 0) + isnull(Yp1.v02, 0) + isnull(Yp1.v03, 0) + isnull(Yp1.v04, 0) + isnull(Yp1.v05, 0) + isnull(Yp1.v06, 0) + isnull(Yp1.v07, 0) + isnull(Yp1.v08, 0) + isnull(Yp1.v09, 0) + isnull(Yp1.v10, 0) + isnull(Yp1.v11, 0) + isnull(Yp1.v12, 0)) FYR_NY,
(isnull(Ym2.v01,0)+isnull(Ym2.v02,0)+isnull(Ym2.v03,0)) q01, (isnull(Ym2.v04,0)+isnull(Ym2.v05,0)+isnull(Ym2.v06,0)) q02, (isnull(Ym2.v07,0)+isnull(Ym2.v08,0)+isnull(Ym2.v09,0)) q03, (isnull(Ym2.v10,0)+isnull(Ym2.v11,0)+isnull(Ym2.v12,0)) q04,
(isnull(Ym1.v01,0)+isnull(Ym1.v02,0)+isnull(Ym1.v03,0)) q05, (isnull(Ym1.v04,0)+isnull(Ym1.v05,0)+isnull(Ym1.v06,0)) q06, (isnull(Ym1.v07,0)+isnull(Ym1.v08,0)+isnull(Ym1.v09,0)) q07, (isnull(Ym1.v10,0)+isnull(Ym1.v11,0)+isnull(Ym1.v12,0)) q08,
(isnull(Y_0.v01,0)+isnull(Y_0.v02,0)+isnull(Y_0.v03,0)) q09, (isnull(Y_0.v04,0)+isnull(Y_0.v05,0)+isnull(Y_0.v06,0)) q10, (isnull(Y_0.v07,0)+isnull(Y_0.v08,0)+isnull(Y_0.v09,0)) q11, (isnull(Y_0.v10,0)+isnull(Y_0.v11,0)+isnull(Y_0.v12,0)) q12,
dbo.dateOnly(getdate()) date_uploaded, 'strata' data_source
from
(select organisation, account, analysis, [year], kpiref, min([month]) [month], min(jobID) jobID from staging_phase_4
where category in ('PLAN', 'PLAN-1', 'PLAN-2', 'PLAN+1', 'ACTUAL-1', 'ACTUAL-2')
and jobID in (select id from dbo.jobs where (name in ('strata @ Actual Average', 'strata @ Plan')))
group by organisation, account, analysis, [year], kpiref) base
left outer join staging_phase_4 Y_0 on Y_0.organisation = base.organisation and Y_0.account = base.account and Y_0.analysis = base.analysis and Y_0.kpiref = base.kpiref and Y_0.category = 'PLAN'
left outer join staging_phase_4 Ym1 on Ym1.organisation = base.organisation and Ym1.account = base.account and Ym1.analysis = base.analysis and Ym1.kpiref = base.kpiref and Ym1.category = 'PLAN-1'
left outer join staging_phase_4 Ym2 on Ym2.organisation = base.organisation and Ym2.account = base.account and Ym2.analysis = base.analysis and Ym2.kpiref = base.kpiref and Ym2.category = 'PLAN-2'
left outer join staging_phase_4 Yp1 on Yp1.organisation = base.organisation and Yp1.account = base.account and Yp1.analysis = base.analysis and Yp1.kpiref = base.kpiref and Yp1.category = 'PLAN+1'
left outer join staging_phase_4 Am1 on Am1.organisation = base.organisation and Am1.account = base.account and Am1.analysis = base.analysis and Am1.kpiref = base.kpiref and Am1.category = 'ACTUAL-1'
left outer join staging_phase_4 Am2 on Am2.organisation = base.organisation and Am2.account = base.account and Am2.analysis = base.analysis and Am2.kpiref = base.kpiref and Am2.category = 'ACTUAL-2'
left outer join jobs_calculations_setvalues sv1 on base.kpiref = sv1.calcid
inner join jobs on base.jobID = jobs.id
cross apply spec.calculated_strategic_accounts(base.account) calc
union
-- *** CURRENT GFO ***
select base.organisation, calc.mm_account, base.analysis,
case base.kpiref when -1 then calc.source_of_value else sv1.value end source_of_value,
base.account, base.[year], 'CURRENT GFO' category,
[currency] currency_code, ltrim(rtrim(right(jobs.name, len(jobs.name) - patindex('%@%', jobs.name)))) forex_rate_type, base.[month],
isnull(Y_0.v01, 0) v01, isnull(Y_0.v02, 0) v02, isnull(Y_0.v03, 0) v03, isnull(Y_0.v04, 0) v04, isnull(Y_0.v05, 0) v05, isnull(Y_0.v06, 0) v06,
isnull(Y_0.v07, 0) v07, isnull(Y_0.v08, 0) v08, isnull(Y_0.v09, 0) v09, isnull(Y_0.v10, 0) v10, isnull(Y_0.v11, 0) v11, isnull(Y_0.v12, 0) v12,
isnull(Y_0.CMO, 0) CMO, (0) forecast_accuracy, isnull(Y_0.YTD, 0) YTD, (0) QTD, isnull(Y_0.FYR, 0) FYR,
(isnull(Ym1.v01, 0) + isnull(Ym1.v02, 0) + isnull(Ym1.v03, 0) + isnull(Ym1.v04, 0) + isnull(Ym1.v05, 0) + isnull(Ym1.v06, 0) + isnull(Ym1.v07, 0) + isnull(Ym1.v08, 0) + isnull(Ym1.v09, 0) + isnull(Ym1.v10, 0) + isnull(Ym1.v11, 0) + isnull(Ym1.v12, 0)) FYR_1,
(isnull(Ym2.v01, 0) + isnull(Ym2.v02, 0) + isnull(Ym2.v03, 0) + isnull(Ym2.v04, 0) + isnull(Ym2.v05, 0) + isnull(Ym2.v06, 0) + isnull(Ym2.v07, 0) + isnull(Ym2.v08, 0) + isnull(Ym2.v09, 0) + isnull(Ym2.v10, 0) + isnull(Ym2.v11, 0) + isnull(Ym2.v12, 0)) FYR_2,
(isnull(Yp1.v01, 0) + isnull(Yp1.v02, 0) + isnull(Yp1.v03, 0) + isnull(Yp1.v04, 0) + isnull(Yp1.v05, 0) + isnull(Yp1.v06, 0) + isnull(Yp1.v07, 0) + isnull(Yp1.v08, 0) + isnull(Yp1.v09, 0) + isnull(Yp1.v10, 0) + isnull(Yp1.v11, 0) + isnull(Yp1.v12, 0)) FYR_NY,
(isnull(Ym2.v01,0)+isnull(Ym2.v02,0)+isnull(Ym2.v03,0)) q01, (isnull(Ym2.v04,0)+isnull(Ym2.v05,0)+isnull(Ym2.v06,0)) q02, (isnull(Ym2.v07,0)+isnull(Ym2.v08,0)+isnull(Ym2.v09,0)) q03, (isnull(Ym2.v10,0)+isnull(Ym2.v11,0)+isnull(Ym2.v12,0)) q04,
(isnull(Ym1.v01,0)+isnull(Ym1.v02,0)+isnull(Ym1.v03,0)) q05, (isnull(Ym1.v04,0)+isnull(Ym1.v05,0)+isnull(Ym1.v06,0)) q06, (isnull(Ym1.v07,0)+isnull(Ym1.v08,0)+isnull(Ym1.v09,0)) q07, (isnull(Ym1.v10,0)+isnull(Ym1.v11,0)+isnull(Ym1.v12,0)) q08,
(isnull(Y_0.v01,0)+isnull(Y_0.v02,0)+isnull(Y_0.v03,0)) q09, (isnull(Y_0.v04,0)+isnull(Y_0.v05,0)+isnull(Y_0.v06,0)) q10, (isnull(Y_0.v07,0)+isnull(Y_0.v08,0)+isnull(Y_0.v09,0)) q11, (isnull(Y_0.v10,0)+isnull(Y_0.v11,0)+isnull(Y_0.v12,0)) q12,
dbo.dateOnly(getdate()) date_uploaded, 'strata' data_source
from
(select organisation, account, analysis, [year], kpiref, min([month]) [month], min(jobID) jobID from staging_phase_4
where category in ('CURRENT GFO', 'ACTUAL-1', 'ACTUAL-2', 'CURRENT GFO+1')
and jobID in (select id from dbo.jobs where (name in ('strata @ Actual Average', 'strata @ Plan')))
group by organisation, account, analysis, [year], kpiref) base
left outer join staging_phase_4 Y_0 on Y_0.organisation = base.organisation and Y_0.account = base.account and Y_0.analysis = base.analysis and Y_0.kpiref = base.kpiref and Y_0.category = 'CURRENT GFO'
left outer join staging_phase_4 Ym1 on Ym1.organisation = base.organisation and Ym1.account = base.account and Ym1.analysis = base.analysis and Ym1.kpiref = base.kpiref and Ym1.category = 'ACTUAL-1'
left outer join staging_phase_4 Ym2 on Ym2.organisation = base.organisation and Ym2.account = base.account and Ym2.analysis = base.analysis and Ym2.kpiref = base.kpiref and Ym2.category = 'ACTUAL-2'
left outer join staging_phase_4 Yp1 on Yp1.organisation = base.organisation and Yp1.account = base.account and Yp1.analysis = base.analysis and Yp1.kpiref = base.kpiref and Yp1.category = 'CURRENT GFO+1'
left outer join jobs_calculations_setvalues sv1 on base.kpiref = sv1.calcid
inner join jobs on base.jobID = jobs.id
cross apply spec.calculated_strategic_accounts(base.account) calc
union
-- *** PREVIOUS RF ***
select base.organisation, calc.mm_account, base.analysis,
case base.kpiref when -1 then calc.source_of_value else sv1.value end source_of_value,
base.account, base.[year], 'PREVIOUS RF' category,
[currency] currency_code, ltrim(rtrim(right(jobs.name, len(jobs.name) - patindex('%@%', jobs.name)))) forex_rate_type, base.[month],
isnull(Y_0.v01, 0) v01, isnull(Y_0.v02, 0) v02, isnull(Y_0.v03, 0) v03, isnull(Y_0.v04, 0) v04, isnull(Y_0.v05, 0) v05, isnull(Y_0.v06, 0) v06,
isnull(Y_0.v07, 0) v07, isnull(Y_0.v08, 0) v08, isnull(Y_0.v09, 0) v09, isnull(Y_0.v10, 0) v10, isnull(Y_0.v11, 0) v11, isnull(Y_0.v12, 0) v12,
isnull(Y_0.CMO, 0) CMO, (0) forecast_accuracy, isnull(Y_0.YTD, 0) YTD, (0) QTD, isnull(Y_0.FYR, 0) FYR,
(isnull(Ym1.v01, 0) + isnull(Ym1.v02, 0) + isnull(Ym1.v03, 0) + isnull(Ym1.v04, 0) + isnull(Ym1.v05, 0) + isnull(Ym1.v06, 0) + isnull(Ym1.v07, 0) + isnull(Ym1.v08, 0) + isnull(Ym1.v09, 0) + isnull(Ym1.v10, 0) + isnull(Ym1.v11, 0) + isnull(Ym1.v12, 0)) FYR_1,
(isnull(Ym2.v01, 0) + isnull(Ym2.v02, 0) + isnull(Ym2.v03, 0) + isnull(Ym2.v04, 0) + isnull(Ym2.v05, 0) + isnull(Ym2.v06, 0) + isnull(Ym2.v07, 0) + isnull(Ym2.v08, 0) + isnull(Ym2.v09, 0) + isnull(Ym2.v10, 0) + isnull(Ym2.v11, 0) + isnull(Ym2.v12, 0)) FYR_2,
(isnull(Yp1.v01, 0) + isnull(Yp1.v02, 0) + isnull(Yp1.v03, 0) + isnull(Yp1.v04, 0) + isnull(Yp1.v05, 0) + isnull(Yp1.v06, 0) + isnull(Yp1.v07, 0) + isnull(Yp1.v08, 0) + isnull(Yp1.v09, 0) + isnull(Yp1.v10, 0) + isnull(Yp1.v11, 0) + isnull(Yp1.v12, 0)) FYR_NY,
(isnull(Ym2.v01,0)+isnull(Ym2.v02,0)+isnull(Ym2.v03,0)) q01, (isnull(Ym2.v04,0)+isnull(Ym2.v05,0)+isnull(Ym2.v06,0)) q02, (isnull(Ym2.v07,0)+isnull(Ym2.v08,0)+isnull(Ym2.v09,0)) q03, (isnull(Ym2.v10,0)+isnull(Ym2.v11,0)+isnull(Ym2.v12,0)) q04,
(isnull(Ym1.v01,0)+isnull(Ym1.v02,0)+isnull(Ym1.v03,0)) q05, (isnull(Ym1.v04,0)+isnull(Ym1.v05,0)+isnull(Ym1.v06,0)) q06, (isnull(Ym1.v07,0)+isnull(Ym1.v08,0)+isnull(Ym1.v09,0)) q07, (isnull(Ym1.v10,0)+isnull(Ym1.v11,0)+isnull(Ym1.v12,0)) q08,
(isnull(Y_0.v01,0)+isnull(Y_0.v02,0)+isnull(Y_0.v03,0)) q09, (isnull(Y_0.v04,0)+isnull(Y_0.v05,0)+isnull(Y_0.v06,0)) q10, (isnull(Y_0.v07,0)+isnull(Y_0.v08,0)+isnull(Y_0.v09,0)) q11, (isnull(Y_0.v10,0)+isnull(Y_0.v11,0)+isnull(Y_0.v12,0)) q12,
dbo.dateOnly(getdate()) date_uploaded, 'strata' data_source
from
(select organisation, account, analysis, [year], kpiref, min([month]) [month], min(jobID) jobID from staging_phase_4
where category in ('PREVIOUS RF', 'ACTUAL-1', 'ACTUAL-2', 'PREVIOUS RF+1')
and jobID in (select id from dbo.jobs where (name in ('strata @ Actual Average', 'strata @ Plan')))
group by organisation, account, analysis, [year], kpiref) base
left outer join staging_phase_4 Y_0 on Y_0.organisation = base.organisation and Y_0.account = base.account and Y_0.analysis = base.analysis and Y_0.kpiref = base.kpiref and Y_0.category = 'PREVIOUS RF'
left outer join staging_phase_4 Ym1 on Ym1.organisation = base.organisation and Ym1.account = base.account and Ym1.analysis = base.analysis and Ym1.kpiref = base.kpiref and Ym1.category = 'ACTUAL-1'
left outer join staging_phase_4 Ym2 on Ym2.organisation = base.organisation and Ym2.account = base.account and Ym2.analysis = base.analysis and Ym2.kpiref = base.kpiref and Ym2.category = 'ACTUAL-2'
left outer join staging_phase_4 Yp1 on Yp1.organisation = base.organisation and Yp1.account = base.account and Yp1.analysis = base.analysis and Yp1.kpiref = base.kpiref and Yp1.category = 'PREVIOUS RF+1'
left outer join jobs_calculations_setvalues sv1 on base.kpiref = sv1.calcid
inner join jobs on base.jobID = jobs.id
cross apply spec.calculated_strategic_accounts(base.account) calc
Check the Views you select your data from, the problem is not on Oracle's side. (see comments ;-)