hi
here is the details: -
Select walkin_id, applying_for from walkins where walkin_id =197
WALKIN_ID APPLYING_FOR
---------------------------
197 CLASS1:CLASS2:OTHER /*stored colon seprated classification*/
Select program_id, program_name, classification from ss_programs
PROGRAM_ID PROGRAM_NAME CLASSIFICATION
---------------------------------------------
1 PG1 CLASS1
2 PG2 CLASS1
3 PG3 CLASS3
4 PG4 CLASS1
5 PG5 CLASS1
6 PG6 CLASS1
7 PG7 CLASS1
8 PG8 CLASS1
9 PG9 CLASS1
10 PG10 CLASS2
11 PG11 CLASS2
12 PG12 CLASS3
13 PG13 CLASS1
14 PG14 CLASS1
15 PG15 CLASS1
16 PG16 CLASS1
17 PG17 CLASS1
18 OTHER OTHER
now i wanted to extract all the program_ids belongs to the walkins.applying_for (colon seprated ss_programs.classification) and
rebuild them into colon separated list
like if
walkins.applying_for is : CLASS1:CLASS2:OTHER then the
program_list would be : PG1:PG2:PG4:PG5:PG6:PG7:PG8:PG9:PG10:PG11:PG13:PG14:PG15:PG16:PG17:OTHER
and for this i build the following query and its giving me
"ORA-01428: argument '-1' is out of range" error and i am stucked.
So could you please tell me where i am doing wrong
and can we do this with SQL like below specified query or some other (SQL) way.
SELECT SUBSTR (LTRIM(MAX (SYS_CONNECT_BY_PATH (program_id, ':')) KEEP (DENSE_RANK LAST ORDER BY curr),
':'), 2) programs
FROM
(SELECT walkin_id, program_id,
row_number() over (partition by 1 order by program_id) curr,
row_number() over (partition by 1 order by program_id)-1 prev
FROM
(SELECT wp.walkin_id, sp.program_id
FROM SS_PROGRAMS sp,
(SELECT walkin_id, substr(val, decode(level, 1, 1, INSTR(val, ':', 1, LEVEL -1)+1),
INSTR(val, ':', 1, level) - decode(level, 1, 1, INSTR(val, ':', 1, LEVEL -1)+1)) walkin_classif
FROM
(SELECT walkin_id, DECODE(SUBSTR(applying_for, -1, 1), ':', applying_for, applying_for||':') val FROM walkins
WHERE walkin_id =197)
CONNECT BY INSTR(val, ':', 1, LEVEL) > 0) wp
WHERE sp.classification = wp.walkin_classif))
GROUP BY walkin_id
CONNECT BY prev = PRIOR curr AND walkin_id = PRIOR walkin_id
START WITH curr = 1;