Hi community,
I have an interesting (I think it is) problem with CONNECT BY, which I'm struggling with for the whole evening now.
I have the below query which works fine in ntw_step1 as long as I only have one row of data in the source table (inpt in the below example) but multiplies results as soon as I submit more. I have fixed this by grouping the result in a second subquery, but believe there should be a more straight forward approach withing the first subquery in the connect by section.
The reason for the multiplication is that the connect by section connects with all 3 datasets but it actually should only connect within the same ref_id. How would I get that into the first query?
WITH inpt AS(
SELECT
123 orig_inpt_num,
1 id_ref
FROM DUAL
UNION ALL SELECT
12000 orig_inpt_num,
2 id_ref
FROM DUAL
UNION ALL SELECT
547546 orig_inpt_num,
3 id_ref
FROM DUAL
),
ntw_step_1 AS(
SELECT
s.id_ref,
TRUNC(MOD(TRUNC(ABS(s.orig_inpt_num)), POWER(1000, LEVEL)) / POWER(1000, LEVEL - 1)) grp,
LEVEL grp_pos
FROM
inpt s
CONNECT BY (LEVEL <= TRUNC(LOG(1000, TRUNC(ABS(s.orig_inpt_num)))) + 1)
)
SELECT
s.id_ref,
s.grp,
s.grp_pos
FROM
ntw_step_1 s
GROUP BY
s.id_ref,
s.grp,
s.grp_pos
Many Thanks in advance!