Приведённый ниже скоипт у меня в отчётах находится на закладке DATA и совершенно нормально отрабатывает, возвращая полноценную таблицу.
Однако сейчас я пытаюсь сделать что-то подобное для Oracle DB, а вот с ним возникают трудности.
_______________________________________________________
declare @SQL_String varchar(max);
declare @SQL_Join varchar(max);
declare @SQL_Where varchar(max);
declare @SQL_Group varchar(max);
declare @SQL_From varchar(100);
declare @SQL_To varchar(100);
declare @SQL_Series varchar(150);
declare @SQL_Inners varchar(150);

set @SQL_Join = '';
set @SQL_Where = '';

if (@sWellsID is not Null and @sWellsID <>'NULL')
or (@sPUID is not Null and @sPUID <>'NULL')
or (@sFieldsID is not Null and @sFieldsID <>'NULL')
or (@sPadsID is not Null and @sPadsID <>'NULL')
or (@sWellSetID is not Null and @sWellSetID<>'NULL')
begin
set @SQL_Join = @SQL_Join +' inner join WellStates WS on (WS.ID = JB.WellStateID)';
if (@sWellsID is not Null and @sWellsID <>'NULL') set @SQL_Where = @SQL_Where + ' and (WS.WellID in ('+@sWellsID +'))';
if (@sPUID is not Null and @sPUID <>'NULL')
or (@sFieldsID is not Null and @sFieldsID <>'NULL')
or (@sPadsID is not Null and @sPadsID <>'NULL')
or (@sWellSetID is not Null and @sWellSetID<>'NULL')
begin
set @SQL_Join = @SQL_Join +' inner join Wells WL on (WL.ID = WS.WellID)';
if @sPUID is not Null and @sPUID <>'NULL' set @SQL_Where = @SQL_Where + ' and (WL.PUID in ('+@sPUID +'))';
if @sFieldsID is not Null and @sFieldsID <>'NULL' set @SQL_Where = @SQL_Where + ' and (WL.FieldID in ('+@sFieldsID+'))';
if @sPadsID is not Null and @sPadsID <>'NULL' set @SQL_Where = @SQL_Where + ' and (WL.PadID in ('+@sPadsID +'))';
if @sWellSetID is not Null and @sWellSetID<>'NULL' begin
set @SQL_Join = @SQL_Join + ' inner join WellSetToWell WSW on WSW.WellID = WL.ID';
set @SQL_Where = @SQL_Where + ' and (WSW.WellSetID in ('+@sWellSetID+'))';
end;
end;
end;

if @sOperationTypesID is not Null and @sOperationTypesID<>'NULL'
set @SQL_Where = @SQL_Where + ' and (JB.OperationTypeID in ('+@sOperationTypesID+'))';
;

if @sFMID is not Null and @sFMID<>'NULL' begin
set @SQL_Join = @SQL_Join + ' inner join FM_Modules FMM on FMM.ID = JB.FM_ModuleID';
set @SQL_Where = @SQL_Where + ' and (FMM.FMID in ('+@sFMID+'))';
end;


set @SQL_From = 'convert(datetime, '''+@dDateFrom+''')';
set @SQL_To = 'convert(datetime, '''+@dDateTo +''')';

if @sGroupingID is not Null and @sGroupingID<>'NULL' begin
select
@SQL_Series = IsNull(Field_Sentence, '''''')
, @SQL_Inners = IsNull(Inner_Sentence, '''''')
from Sorting
where ID = @sGroupingID;
select @SQL_Series = replace(@SQL_Series, '%jobdate%', 'max(JO.EndDate)');
-- replace(@SQL_Inners, '%jobdate%', 'max(JO.EndDate)');
end
else begin
set @SQL_Series = '''''';
set @SQL_Inners = '''''';
end;


set @SQL_String = '
-- Объявление таблицы для заполнения долларом на баррель
declare @Scorpion table (
RecNum Integer
, SeriesText varchar(100) -- Текст, разграничивающий данные по сериям
, OrdersText varchar(100) -- Текст, определяющий порядок точек в серии
, Cost float -- Стоимость данного мероприятия
, IOV float -- ДД данного мероприятия
, cCost float -- Накопленная стоимость в порядке OrdersText
, cIOV float -- Накопленная ДД в порядке OrdersText
, COSTperIOV float -- Удельная стоимость (для упорядочивания)
);

insert into @Scorpion
select ROW_NUMBER() over (order by
'+@SQL_Series+'
, sign(case when JI.Oil is Null then 0 else JI.Oil end) DESC
, case when (JI.Oil is Null) or (JI.Oil=0) then sum(JO."Sum") else sum(JO."Sum")/JI.Oil end
, JB.ID)
, '+@SQL_Series+'
, convert(varchar(100), JB.ID)
, sum(dbo.GetMoney(JO."Sum", JO.SumCurrencyID, '+@sCurrenciesID+', JO.EndDate))
, case
when JI.Oil is Null then 0 else /*case
when abs(JI.Oil)>10000000 then sign(JI.Oil)*10000000 else*/ JI.Oil /*end */
end
, convert(float, 0)
, convert(float, 0)
, case when (JI.Oil is Null) or (JI.Oil=0) then Null else sum(JO."Sum")/JI.Oil end
from Jobs JB
inner join OperationTypes OT on (OT.ID = JB.OperationTypeID)
inner join JobOperations JO on (JO.JobID = JB.ID)
'+@SQL_Join+'
inner join Job_FCs JFC on (JFC.JobID = JB.ID) and (JFC.FC_TypeID = '+@sFC_TypesID+') and (JFC.KLevel = OT.TypeLevel)
left join Job_IOR JI on (JI.Job_FCID = JFC.ID) and (JI.PeriodID = '+@sPeriod_IORID+')
where (0=0)
'+@SQL_Where+'
group by JB.ID, JI.Oil
having (max(JO.EndDate) >= '+@SQL_From+') and (max(JO.EndDate) < '+@SQL_To+')
order by 1
-- '+@SQL_Series+'
-- , sign(case when JI.Oil is Null then 0 else JI.Oil end) DESC
-- , case when (JI.Oil is Null) or (JI.Oil=0) then sum(JO."Sum") else sum(JO."Sum")/JI.Oil end
-- , JB.ID
;

declare @CD float;
declare @CB float;
declare @ST varchar(100);
set @CD = 0;
set @CB = 0;
set @ST = '''';

update @Scorpion
set @CD = cCost = isnull(Cost, 0) + case when @ST = SeriesText then isnull(@CD, 0) else 0 end
, @CB = cIOV = isnull(IOV, 0) + case when @ST = SeriesText then isnull(@CB, 0) else 0 end
, @ST = SeriesText
;

insert into @Scorpion (RecNum, SeriesText, cCost, cIOV)
select distinct 0, SeriesText, 0, 0
from @Scorpion;

select SeriesText, OrdersText, cIOV as "CumIOV", cCost as "CumCost"
from @Scorpion
order by SeriesText, RecNum, OrdersText;
'
;

exec(@SQL_String);