【Question】

I have a table called rbd_dtl. The table structure is as follows:

RBD_ID ODFEE_ID RBD_VALUE
25 206 ALL
31 205 A
26 205 B
41 205 C
42 205 D
30 205 E
38 205 F
39 205 H
27 205 J
35 205 K
32 205 N
36 205 O
28 205 Q
44 205 R
34 205 S
29 205 U
33 205 V
40 205 X
37 205 Y
43 205 Z

My requirement is to write a PLSQL function which returns a character.
The column RBD_VALUE contains values as ALL or from A to Z.
If the column value is ALL then I return the same;
If not - as you can see the data above there in the table, the following alphabets are missing for 205 odfee_id...
G, I, L, M, P, T, W
It should return as A-F,H,J-K,N-O,Q-S,U-V,X-Z
Based on the above data fromm A to F we can see all consecutive alphabets but after F there is no G so we have to show as A-F.
The next is H and since I is missing, we pick H.
After H it starts from J, K and since there is no consecutive character follows, we pick J-K.

【Answer】

You can use a window function to get intervals of the letters. But the process is difficult. A simple and concise way is to use SPL (Structured Process Language):

A1: Read desired data from the table.

A2: Align RBD_VALUE with the 26 English letters.

A3: Perform a merge grouping by putting neighboring consecutive members into one group.

A4: Select non-empty groups.

A5: Get the first letter and the last letter from each group and join them up with a “-“, and join up the groups with commas to return as a string:

About how you call an SPL script in another application, see How to Call an SPL Script in Java.