CODEWITHSUNDEEP
sqloracle-databaserecursion
Yrogirg
Yrogirg

Reputation: 2373

How do I make recursion in SQL only with select?

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()?

Upvotes: 0

Views: 407

Answers (2)

aF.
aF.

Reputation: 66697

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

Upvotes: -1

user359040
user359040

Reputation:

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).

Upvotes: 2

Related Questions