Please tell me what condition of the problem can be thought up so that it can be solved only by recursion, not connect by. For example, you can do something like There is a list of employees, the coefficient of salary increase every year, his current salary For each of them, draw up a schedule for 20 years ahead of salaries, given that if the total salary of the entire department exceeds a million, then 10% is subtracted from each salary, and 10% is added to this million
My table
create table scrubs5(name varchar2(100),cent number, salary number);
insert into scrubs5
values ('John Dorian' ,10,50000);
insert into scrubs5
values ('Eliot Reed',15 ,40000);
insert into scrubs5
values ('Pesivald Cox',10, 35000);
insert into scrubs5
values ('Bob Kelso' ,20,30000);
insert into scrubs5
values ('Cris Terk',15, 25000);Code
WITH data( sal, name, n, cent) AS (
SELECT salary as sal, name, 1 as n, cent
FROM scrubs5
UNION ALL
SELECT sal + sal*cent/100, name, n+1, cent
FROM data
WHERE n < 5
),
data1(sal, name, n, cent, t) as
(SELECT sal, name, n, cent, 100000 as t FROM data
union all
select case when sum(sal) over (partition by n)> t then sal- sal*cent/100 else sal end, name,n+1, cent, case when sum(sal) over (partition by n)> t then t+t*10/100 else t end
from data1
WHERE n < 5)
select sal, name, n, cent, t from data1
order by n
;
I'm a little unclear what you're asking here.
it can be solved only by recursion, not connect byCONNECT BY is a recursive query!
If you're asking what problems must you use recursive WITH for instead of CONNECT BY, arguably both can do the same things. There are some classes of problems you'd need to combine CONNECT BY with other clauses that you can do purely using recursive WITH though.
Cases like these include:
The running product (repeated multiplication) - you can get close summing logarithms, but this has rounding errors
Running totals that change or reset based on arbitrary critiera - e.g. when the counter > 100, reset back to zero
The problem you've described has both of these properties:
Percentage increases/decreases are a form of repeated multiplication
The percentage change varies based on the values of other expressions for the current row
In terms of solving the problem you've asked, I'm guessing something like this should work:
with data( sal, name, n, cent) as (
select salary as sal, name, 1 as n, cent
from scrubs5
), data1 ( sal, new_sal, name, n, cent, t ) as(
select sal, sal new_sal, name, n, cent, 1000000 as t
from data
union all
select sal,
case
when sum(new_sal) over (partition by n) < t
then round ( new_sal * ( 1 + ( cent / 100 ) ) )
else round ( new_sal * 0.9 )
end,
name, n+1, cent,
case
when sum(new_sal) over (partition by n) < t
then t
else t * 1.10
end t
from data1
where n < 20
)
select new_sal, name, n, cent, t,
sum ( new_sal ) over ( partition by n ) year_tot
from data1
order by n, name;
NEW_SAL NAME N CENT T YEAR_TOT
---------- -------------------- ---------- ---------- ---------- ----------
30000 Bob Kelso 1 20 1000000 180000
25000 Cris Terk 1 15 1000000 180000
40000 Eliot Reed 1 15 1000000 180000
50000 John Dorian 1 10 1000000 180000
35000 Pesivald Cox 1 10 1000000 180000
36000 Bob Kelso 2 20 1000000 204250
28750 Cris Terk 2 15 1000000 204250
46000 Eliot Reed 2 15 1000000 204250
55000 John Dorian 2 10 1000000 204250
38500 Pesivald Cox 2 10 1000000 204250
...
374394 Bob Kelso 18 20 1210000 1090416
164777 Cris Terk 18 15 1210000 1090416
263638 Eliot Reed 18 15 1210000 1090416
169180 John Dorian 18 10 1210000 1090416
118427 Pesivald Cox 18 10 1210000 1090416
449273 Bob Kelso 19 20 1210000 1258319
189494 Cris Terk 19 15 1210000 1258319
303184 Eliot Reed 19 15 1210000 1258319
186098 John Dorian 19 10 1210000 1258319
130270 Pesivald Cox 19 10 1210000 1258319
404346 Bob Kelso 20 20 1331000 1132488
170545 Cris Terk 20 15 1331000 1132488
272866 Eliot Reed 20 15 1331000 1132488
167488 John Dorian 20 10 1331000 1132488
117243 Pesivald Cox 20 10 1331000 1132488