PS The answers provided did not help. The error still persists. Need help if someone can still help :)
Good day. The package compiles successfully, and when I call it through a job, I get the failed status and an error
"ORA-00937: not a single-group group function ORA-06512: at "DMA.FILL_F101_ROUND_F", line 42 ORA-06512: at line 2 "
Prompt my carelessness, I use group by on non-group parameters, but the error does not go away. I understand that when using group aggregators in the select, it is necessary to indicate not group aggregators in the group. But in the end it doesn't work when calling the package. I read similar questions and everywhere it is advised to reconsider group by. I seem to have used all possible options, but did not help. Thanks in advance!
group by
trunc(i_OnDate, 'mm'),
last_day(i_OnDate),
s.chapter,
substr(acc_d.account_number, 1, 5),
acc_d.char_type;
My pck:
create or replace package body dma.fill_f101_round_f is
----------------------------------------------------------------------------------------------------
procedure "$Rev: 220707 $" is begin null; end;
procedure "$URL:: https://svn.neoflex.ru$" is begin null; end;
procedure "$Author:: rbubnov $" is begin null; end;
procedure "$Date:: 2018-12-22 12:09:59 #$" is begin null; end;
----------------------------------------------------------------------------------------------------
procedure Log
( i_message in varchar2
)
is
begin
dma.logger.writeLog('[' || c_MartName || '] ' || i_message);
end;
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
procedure fill
( i_OnDate in date
)
is
begin
Log( '[BEGIN] fill(i_OnDate => date '''
|| to_char(i_OnDate, 'yyyy-mm-dd')
|| ''');'
);
Log( 'delete on_date = '
|| to_char(i_OnDate, 'yyyy-mm-dd')
);
delete
from dma.DM_F101_ROUND_F f
where trunc(i_OnDate, 'mm') = from_date
and last_day(i_OnDate) = to_date;
Log('insert');
insert
into dma.dm_f101_round_f f
( from_date
, to_date
, chapter
, ledger_account
, characteristic
, balance_in_rub
, balance_in_val
, balance_in_total
, turn_deb_rub
, turn_deb_val
, turn_deb_total
, turn_cre_rub
, turn_cre_val
, turn_cre_total
, balance_out_rub
, balance_out_val
, balance_out_total
)
select
trunc(i_OnDate, 'mm') as from_date,
last_day(i_OnDate) as to_date,
s.chapter as chapter,
substr(acc_d.account_number, 1, 5) as ledger_account,
acc_d.char_type as characteristic,
-- RUB balance
sum( case
when cur.currency_code in ('643', '810')
then b.balance_out
else 0
end
) as balance_in_rub,
-- VAL balance converted to rub
sum( case
when cur.currency_code not in ('643', '810')
then b.balance_out * exch_r.reduced_cource
else 0
end
) as balance_in_val,
-- Total: RUB balance VAL converted to rub
sum( case
when cur.currency_code in ('643', '810')
then b.balance_out
else b.balance_out * exch_r.reduced_cource
end
) as balance_in_total ,
-- RUB debet turnover
sum(case
when cur.currency_code in ('643', '810')
then at.debet_amount_rub
else 0
end
) as turn_deb_rub,
-- VAL debet turnover converted
sum(case
when cur.currency_code not in ('643', '810')
then at.debet_amount_rub
else 0
end
) as turn_deb_val,
-- SUM = RUB debet turnover VAL debet turnover converted
sum(at.debet_amount_rub) as turn_deb_total,
-- RUB credit turnover
sum(case
when cur.currency_code in ('643', '810')
then at.credit_amount_rub
else 0
end
) as turn_cre_rub,
-- VAL credit turnover converted
sum(case
when cur.currency_code not in ('643', '810')
then at.credit_amount_rub
else 0
end
) as turn_cre_val,
-- SUM = RUB credit turnover VAL credit turnover converted
sum(at.credit_amount_rub) as turn_cre_total,
sum( case
when cur.currency_code in ('643','810') and acc_d.char_type = 'A'
then
sum(case
when cur.currency_code in ('643', '810')
then b.balance_out
else 0
end
) -
sum(case
when cur.currency_code in ('643', '810')
then at.credit_amount_rub
else 0
end
)
sum(case
when cur.currency_code in ('643', '810')
then at.debet_amount_rub
else 0
end
)
when cur.currency_code in ('643','810') and acc_d.char_type = 'P'
then
sum(case
when cur.currency_code in ('643', '810')
then b.balance_out
else 0
end
)
sum(case
when cur.currency_code in ('643', '810')
then at.credit_amount_rub
else 0
end
) -
sum(case
when cur.currency_code in ('643', '810')
then at.debet_amount_rub
else 0
end
)
else 0
end
) as balance_out_rub,
sum( case
when cur.currency_code not in ('643', '810') and acc_d.char_type = 'A'
then
sum( case
when cur.currency_code not in ('643', '810')
then b.balance_out * exch_r.reduced_cource
else 0
end
) -
sum( case
when cur.currency_code not in ('643', '810')
then at.credit_amount_rub
else 0
end
)
sum( case
when cur.currency_code not in ('643', '810')
then at.debet_amount_rub
else 0
end
)
when cur.currency_code not in ('643', '810') and acc_d.char_type = 'P'
then
sum( case
when cur.currency_code not in ('643', '810')
then b.balance_out * exch_r.reduced_cource
else 0
end
)
sum( case
when cur.currency_code not in ('643', '810')
then at.credit_amount_rub
else 0
end
)-
sum( case
when cur.currency_code not in ('643', '810')
then at.debet_amount_rub
else 0
end
)
else 0
end
) as balance_out_val,
cast(null as number) as balance_out_total
from ds.md_ledger_account_s s
join ds.md_account_d acc_d
on substr(acc_d.account_number, 1, 5) = s.ledger_account
join ds.md_currency_d cur
on cur.currency_rk = acc_d.currency_rk
left
join ds.ft_balance_f b
on b.account_rk = acc_d.account_rk
and b.on_date = trunc(i_OnDate, 'mm') - 1
left
join ds.md_exchange_rate_d exch_r
on exch_r.currency_rk = acc_d.currency_rk
and i_OnDate between exch_r.data_actual_date and exch_r.data_actual_end_date
left
join dma.dm_account_turnover_f at
on at.account_rk = acc_d.account_rk
and at.on_date between trunc(i_OnDate, 'mm') and last_day(i_Ondate)
where i_OnDate between s.start_date and s.end_date
and i_OnDate between acc_d.data_actual_date and acc_d.data_actual_end_date
and i_OnDate between cur.data_actual_date and cur.data_actual_end_date
group by
trunc(i_OnDate, 'mm'),
last_day(i_OnDate),
s.chapter,
substr(acc_d.account_number, 1, 5),
acc_d.char_type;
update dm_f101_round_f f set
f.balance_out_total = f.balance_out_val f.balance_out_rub;
Log('[END] inserted ' || to_char(sql%rowcount) || ' rows.');
commit;
end;
----------------------------------------------------------------------------------------------------
end fill_f101_round_f;
/```
CodePudding user response：
You have missed out some columns in your grouping. You are also sum'ing a sum, which makes things trickier. That said, the logic of your sum of sums is dependent on one of the columns you are already grouping so you can simplify:
acc_d.char_type,
sum( case
when cur.currency_code in ('643','810') and acc_d.char_type = 'A'
then
sum(case
when cur.currency_code in ('643', '810')
then b.balance_out
else 0
end
) -
sum(case
when cur.currency_code in ('643', '810')
then at.credit_amount_rub
else 0
end
)
sum(case
when cur.currency_code in ('643', '810')
then at.debet_amount_rub
else 0
end
)
when cur.currency_code in ('643','810') and acc_d.char_type = 'P'
then
sum(case
when cur.currency_code in ('643', '810')
then b.balance_out
else 0
end
)
sum(case
when cur.currency_code in ('643', '810')
then at.credit_amount_rub
else 0
end
) -
sum(case
when cur.currency_code in ('643', '810')
then at.debet_amount_rub
else 0
end
)
else 0
end
) as balance_out_rub
...
group by acc_d.char_type...
can become
acc_d.char_type,
case when acc_d.char_type = 'A'
then
sum(case
when cur.currency_code in ('643', '810')
then b.balance_out
else 0
end
) -
sum(case
when cur.currency_code in ('643', '810')
then at.credit_amount_rub
else 0
end
)
sum(case
when cur.currency_code in ('643', '810')
then at.debet_amount_rub
else 0
end
)
when acc_d.char_type = 'P'
then
sum(case
when cur.currency_code in ('643', '810')
then b.balance_out
else 0
end
)
sum(case
when cur.currency_code in ('643', '810')
then at.credit_amount_rub
else 0
end
) -
sum(case
when cur.currency_code in ('643', '810')
then at.debet_amount_rub
else 0
end
)
else 0
end as balance_out_rub
...
group by acc_d.char_type...
Of course, you may be able to simplify it even further.
CodePudding user response：
All non-aggregated columns must be contained in group by clause, not only some of them.
Currently, this is select
select
trunc(i_OnDate, 'mm') as from_date,
last_day(i_OnDate) as to_date,
s.chapter as chapter,
substr(acc_d.account_number, 1, 5) as ledger_account,
acc_d.char_type as characteristic
and this is group by:
group by
s.chapter,
substr(acc_d.account_number, 1, 5),
acc_d.char_type
Should've been
group by
trunc(i_OnDate, 'mm'),
last_day(i_OnDate),
s.chapter,
substr(acc_d.account_number, 1, 5),
acc_d.char_type