query next value [message #658879] |
Thu, 29 December 2016 08:22 |
oracle_coorgi
Messages: 188 Registered: September 2006 Location: INDIA-karnataka
|
Senior Member |
|
|
Hi professionals
i have an requirement ,is there anyway i can achieve this
1
23
456
78910
1112131415
161718192021
...............
hoop next value with one increment....
1
34
678
10111213
...........
skip one number eg:2,5,9 is skipped after each hoop
Warm Regards
coorgi
|
|
|
|
|
Re: query next value [message #658884 is a reply to message #658880] |
Thu, 29 December 2016 08:28 |
oracle_coorgi
Messages: 188 Registered: September 2006 Location: INDIA-karnataka
|
Senior Member |
|
|
Hi professionals
i have an requirement ,is there anyway i can achieve this
1
23
456
78910
1112131415
161718192021
...............
hoop next value with one increment....
1
34
678
10111213
...........
skip one number eg:2,5,9 is skipped after each hoop
Warm Regards
coorgi
|
|
|
|
|
Re: query next value [message #658888 is a reply to message #658879] |
Thu, 29 December 2016 10:42 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Recursive query solution:
with r(
val,
n,
l
) as (
select '1',
1,
1
from dual
union all
select (
select replace(sys_connect_by_path(n + level,','),',')
from dual
where connect_by_isleaf = 1
connect by level <= l + 1
),
n + l + 1,
l + 1
from r
where l <= 10
)
select val
from r
where l <= 10
order by l
/
VAL
--------------------
1
23
456
78910
1112131415
161718192021
22232425262728
2930313233343536
373839404142434445
46474849505152535455
10 rows selected.
SQL>
SY.
|
|
|
Re: query next value [message #658889 is a reply to message #658888] |
Thu, 29 December 2016 11:00 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Hierarchy + analytics:
with t as (
select level l,
sum(level - 1) over(order by level) x
from dual
connect by level <= 10
)
select replace(sys_connect_by_path(x + level,','),',') val
from t
where connect_by_isleaf = 1
connect by prior x = x
and prior sys_guid() is not null
and level <= l
/
VAL
---------------------
1
23
456
78910
1112131415
161718192021
22232425262728
2930313233343536
373839404142434445
46474849505152535455
10 rows selected.
SQL>
SY.
|
|
|
|