0

Is there a way to have a recursion in Oracle SQL only using select statements?

For example how do I sum a column in a table without using sum()?

Alex K.
  • 171,639
  • 30
  • 264
  • 288
Yrogirg
  • 2,301
  • 3
  • 22
  • 33
  • Can you give an example using a dummy dataset? Recursion *is* possible in Oracle using CTEs, but the table structure is needed to give you a meaningful example. Also, it's not really used to just replace SUM(), so an example data-set will give a clue as to whether this is the best approach. – MatBailie Dec 07 '11 at 11:14
  • I don't mean this to be the best solution, it is for educational purposes. If you need a dataset, let it be a table with a single column called VAL and the data itslelf is 1 3 56 7 98 12 – Yrogirg Dec 07 '11 at 11:19
  • Which version of Oracle? *Recursive* CTEs only came in with 11g. –  Dec 07 '11 at 11:35
  • There is no way any sane person would attempt to use recursion to sum a column (except for a sub-tree of a table which has a 1:N relationship to itself). – symcbean Dec 07 '11 at 12:42
  • In SQL, recursion is used to map relationships where the parent and child are the same table. That's not the case in your theoretical scenario, so it's not the correct tool to use for this job. The right way to do this is to use `sum`. I'm not sure what the point is of asking for a convoluted and inefficient solution to a problem with a known simple and efficient solution. – Allan Dec 07 '11 at 13:23

2 Answers2

2

The answer to this question demonstrates recursion in Oracle, for both recursive CTEs (Oracle 11 onwards) and Oracle's own CONNECT BY syntax (Oracle 10g and earlier).

Community
  • 1
  • 1
-1
create table #temp (
id int,
value int)


insert into #temp values (1, 1)
insert into #temp values (2, 2)
insert into #temp values (3, 3)

declare @sum int
declare @biggest int
select @biggest = max(id) from #temp
select @sum = 0

while @biggest != 0
begin
  select @sum = @sum + value from #temp where id = @biggest
  select @biggest = @biggest - 1
end

select @sum
Mark Pim
  • 9,898
  • 7
  • 40
  • 59
aF.
  • 64,980
  • 43
  • 135
  • 198
  • That's not recursion though. I've no idea why recursion *specifically* is required, but it's what the OP is asking for... – MatBailie Dec 07 '11 at 11:23
  • @Dems humm and the while cycle? Can it be considered recursion? – aF. Dec 07 '11 at 11:53
  • Recursion is when an entity invokes itself (usually with a different set of arguments). Loops do have something in common with recursion but, to my knowledge, they have never been considered so. – Andriy M Dec 07 '11 at 14:11