Hello Experts,
DB Versions: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
I have got a scenario like below:
input: 11
output:
11
21
1211
111221
312211
13112221
1113213211
Logic:
First line is printed as it is,
Second line is the summary of the First line and goes like count of same character and the character itself. Here, there are 2 1's and hence it is 21
Third line is the summary of the Second line and goes like count of same character and the character itself. Here, there are 1 2's followed by 1 1's and hence it is 1211
Forth line is the summary of the Third line and goes like count of same character and the character itself. Here, there are 1 1's followed by 1 2's followed by 2 1's and hence it is 111221
and so on.
I want this series to stop when I say like '21' and occurrence 3. Meaning it should stop at 6th line which has 21 in the end i.e., there are 2 1's in the end of this line for the 3rd time, (2 1's in the end of 2nd line would be 1st occurrence, 2 1's in the end of 4nd line would be 2nd occurrence )
I started off something like below:
with inputs as
(
select '11' str from dual
),
r (str, output) as
(
select str, listagg(length(l.ch) || '' || substr(l.ch, 1, 1), '') within group (order by l.lvl) output
from inputs t,
lateral
(
select regexp_substr(t.str, '(.)\1*', 1, level) ch, level lvl
from dual
connect by level <= regexp_count(t.str, '(.)\1*')
) l
group by str
union all
select output, listagg(length(l.ch) || '' || substr(l.ch, 1, 1), '') within group (order by l.lvl) output
from r t,
lateral
(
select regexp_substr(t.output, '(.)\1*', 1, level) ch, level lvl
from dual
connect by level <= regexp_count(t.output, '(.)\1*')
) l
--where some condition to stop this
group by output
)
select * from r
And I am getting the below error :
ORA-32486: unsupported operation in recursive branch of recursive WITH clause
Kindly help on this.
Note: I have asked a similar question on this forum that can be found in the below thread. This question is just the extension of it.
Summarising a text field in Oracle sql
Regards,
Ranagal