Today we will take a look on creation of random strings using mask. It is very helpful when you output string based on some input value should change on-line. This functionality can be assumed by using SQL MODEL clause magic, that allows to generate from many rows one row with output(flat) .
Lets look on an example and you will understand what I mean
Users want to create 2 different package labels from one package number.
Input: Package number = 57896854742
Output:First label = 5/789-685-4742
Second label =[578|968|547|42]
For those who wants to see immediate final query click here
Whole process of creation output can be divided into 2 parts:
- Find what mask format you should use
- Make a query. No procedures or functions, just not so simple query
To create mask I will use next notation: ‘x’ will mean number, and other chars “/[|" - mask characters.
First mask = x/xxx-xxx-xxxx
Second mask=[xxx|xxx|xxx|xx]
Query logic will be next:
- Split mask string into rows. One character in a row
- Replace ‘x’ in mask with subsequent input number
- Use MODEL clause to create output string
Lets take a closer look on query:
Split mask string into rows
SELECT LEVEL position,
substr('x/xxx-xxx-xxxx', LEVEL, 1) val
FROM dual
CONNECT BY LEVEL <= length('x/xxx-xxx-xxxx');
POSITION VAL
---------- ---
1 x
2 /
3 x
4 x
5 x
6 -
7 x
8 x
9 x
10 -
11 x
12 x
13 x
14 x
14 rows selected
I use SUBSTR function with LEVEL clause to get one character in row. LEVEL is used like index of actual split character.
Replace ‘x’ in mask with subsequent input number
SELECT t.position,
CASE val
WHEN 'x' THEN
substr('57896854742',
rank() over(PARTITION BY val ORDER BY position), 1)
ELSE
val
END val,
val AS temp,
rank() over(PARTITION BY val ORDER BY position) rank
FROM (SELECT LEVEL position,
substr('x/xxx-xxx-xxxx', LEVEL, 1) val
FROM dual
CONNECT BY LEVEL <= length('x/xxx-xxx-xxxx')) t
ORDER BY position;
POSITION VAL TEMP RANK
---------- --- ---- ----------
1 5 x 1
2 / / 1
3 7 x 2
4 8 x 3
5 9 x 4
6 - - 1
7 6 x 5
8 8 x 6
9 5 x 7
10 - - 2
11 4 x 8
12 7 x 9
13 4 x 10
14 2 x 11
14 rows selected
First of all look on a RANK() column. Function RANK() returns for each occurence of ‘x’ in VAL column its rank number and it is equal to a position number of character in Input string. After using RANK() function I know exactly what ‘x’ should be replaced with wich character from Input string.
Next use CASE statement to check if value in VAL column is equal to ‘x’. If yes – replace ‘x’ with SUBSTRinged character from Input, if no – put character from mask.
Use MODEL clause to create output string
SELECT position,
output
FROM (SELECT t.position,
CASE val
WHEN 'x' THEN
substr('57896854742',
rank() over(PARTITION BY val ORDER BY position ASC), 1)
ELSE
val
END val
FROM (SELECT LEVEL position,
substr('x/xxx-xxx-xxxx', LEVEL, 1) val
FROM dual
CONNECT BY LEVEL <= length('x/xxx-xxx-xxxx')) t
ORDER BY position)
MODEL
RETURN UPDATED ROWS
DIMENSION BY(position)
MEASURES (to_char(val) AS tmp,'x/xxx-xxx-xxxx' AS output)
RULES
(output[ANY] = output[ CV() - 1] || tmp[cv()]);
POSITION OUTPUT
---------- --------------
1 5
2 5/
3 5/7
4 5/78
5 5/789
6 5/789-
7 5/789-6
8 5/789-68
9 5/789-685
10 5/789-685-
11 5/789-685-4
12 5/789-685-47
13 5/789-685-474
14 5/789-685-4742
14 rows selected
And here we can see a Magic of SQL MODEL clause.
MODEL clause was added to Oracle 10g and described as:
The Model clause defines a multidimensional array by mapping the columns of a query into three groups: partitioning, dimension, and measure columns
Speaking simpler – MODEL clause add spreadsheet functionality to PL/SQL based on DIMENSIONS, MEASURES and RULES. Rules show start of the formulas, and DIMENSION and MEASURES define dataset.
To see difference between Dimension and Measures I use 2 simple rules:
- You cannot change Dimension value in RULES formulas
- You use Dimension value to select proper cell from Measures
RULES part in our example works exactly as Excel fill handle: For each Output cell take value from previous Output cell and concatenate it with actual split character. [ANY] clause works as wild card and CV() returns actual value of cell. Look on OUTPUT row and you will see how string growing for each row, just like in Excel. And here comes Final query…
Final query
SELECT output FROM
(SELECT position,
output
FROM (SELECT t.position,
CASE val
WHEN 'x' THEN
substr('&input',
rank() over(PARTITION BY val ORDER BY position ASC), 1)
ELSE
val
END val
FROM (SELECT LEVEL position,
substr('&mask', LEVEL, 1) val
FROM dual
CONNECT BY LEVEL <= length('&mask')) t
ORDER BY position)
MODEL
RETURN UPDATED ROWS
DIMENSION BY(position)
MEASURES (to_char(val) AS tmp,'&mask' AS output)
RULES
(output[ANY] = output[ CV() - 1] || tmp[cv()])
ORDER BY position DESC)
WHERE ROWNUM=1;
OUTPUT
--------------
5/789-685-4742
----------------
[578|968|547|42]
Try this query entering your own Input and Mask values, or use from example.
ORDER BY clause was added because I found that if I want to get Output value using directly POSITION=14 Oracle query optimizer will try to get value of Output cell earlier that MODEL clause run. To avoid it, use next: first select from subquery all MODEL’led values, next ORDER’ed descending and next simply select first value from subquery.
And that’s all… just not so simple query
To fully understand MODEL clause you can read next articles:
Oracle MODEL clause white paper
SQL Snippets
PSOUG
vkamys
