SQL MODEL clause magic

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:

  1. Find what mask format you should use
  2. 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:

  1. You cannot change Dimension value in RULES formulas
  2. 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

This entry was posted in Not so simple query, oracle, pl/sql and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s