NULLIF – A tiny, powerfull and underrated SQL Function

19 Monday Jun 2017

Posted by troroblog in ORACLE, SQL, Tipps and Tricks, Underestimated Features

Tags

NULLIF has been introducde quite a while ago (Oracle 9) but still this function is not well known to the community.

SYNTAX : NULLIF(expr1,expr2)

Nullif compares expr1 and expr2. If they are equal, then NULL is returned otherwise expr1. We cannot specify the literal NULL for expr1.

So where can this function be used?

Eliminiate Division by Zero:

WITH sales (prod, thisyear, lastyear)
        AS (SELECT 'Monitor' ,  50, 25 FROM dual UNION ALL
            SELECT 'Keyboard', 125,  0 FROM dual UNION ALL
            SELECT 'Mouse'   ,  35, 40 FROM dual UNION ALL 
            SELECT 'Desktop' ,   0, 25 FROM dual UNION ALL
            SELECT 'Laptop'  ,  10,  3 FROM dual)
SELECT prod, thisYear, lastYear
     , ROUND(thisYear * 100 / NULLIF(lastYear,0),2) AS SalesPerc
  FROM sales
/

PROD       THISYEAR   LASTYEAR  SALESPERC
-------- ---------- ---------- ----------
Monitor          50         25        200
Keyboard        125          0
Mouse            35         40       87.5
Desktop           0         25          0
Laptop           10          3     333.33

Remove not needed concatenation seperators:

Add first letter of the middle name followed by a dot if a middle name exists.

WITH pers (firstname, middle, lastname)
       AS (SELECT 'Kurt'  ,'Heinrich','Meier' FROM dual UNION ALL
           SELECT 'Hubert',NULL      ,'Huber' FROM dual)
SELECT firstname || NULLIF(' '||SUBSTR(middle,1,1) || '.',' .') 
    || ' ' || lastname AS fullname
  FROM pers
/    

FULLNAME
---------------
Kurt H. Meier
Hubert Huber

Suppress values above (or below) a given margin:

Do not show salaries greater/equal 3000.

SELECT ename, NULLIF(sal,GREATEST(sal,3000)) AS sal
  FROM emp
/    

ENAME             SAL
---------- ----------
SMITH             800
ALLEN            1600
WARD             1250
JONES            2975
MARTIN           1250
BLAKE            2850
CLARK            2450
SCOTT
KING
TURNER           1500
ADAMS            1100
JAMES             950
FORD
MILLER           1300

As you can see there are multiple opportunities to make use of this smart little function.

Ignore Nulls with LAST_VALUE

16 Sunday Oct 2016

Posted by troroblog in ORACLE, SQL, Tipps and Tricks

≈ Leave a comment

Tags

Analytic Functions, FIRST_VALUE, LAST_VALUE, SQL

A powerfull feature of the LAST_VALUE/FIRST_VALUE analytic function is the IGNORE NULL clause. This feature allows us to inherit a column value from a prio row with a specific attribute.

Just lately I came accross the following problem on ORACLE SQL & PL/SQL support forum:

The Data:

WITH data (skp_id, code_status, date_app) 
       AS (SELECT 111, 'REJECTED', DATE '2016-01-01' FROM dual UNION ALL
           SELECT 123, 'APPROVED', DATE '2016-02-01' FROM dual UNION ALL
           SELECT 201, 'APPROVED', DATE '2016-03-01' FROM dual UNION ALL
           SELECT 302, 'REJECTED', DATE '2016-04-01' FROM dual UNION ALL
           SELECT 403, 'REJECTED', DATE '2016-05-01' FROM dual UNION ALL
           SELECT 450, 'APPROVED', DATE '2016-06-01' FROM dual UNION ALL
           SELECT 475, 'APPROVED', DATE '2016-07-01' FROM dual UNION ALL
           SELECT 480, 'APPROVED', DATE '2016-08-01' FROM dual UNION ALL
           SELECT 490, 'REJECTED', DATE '2016-09-01' FROM dual) 
SELECT skp_id
     , code_status
     , date_app
 FROM data
/

The task was to show with every line the last rejection date prior to the current row as an additional column.

SKP_ID	CODE_STATUS	DATE_APP	LAST_REJECTED
111	REJECTED	01-JAN-16	–
123	APPROVED	01-FEB-16	01-JAN-16
201	APPROVED	01-MAR-16	01-JAN-16
302	REJECTED	01-APR-16	01-JAN-16
403	REJECTED	01-MAY-16	01-APR-16
450	APPROVED	01-JUN-16	01-MAY-16
475	APPROVED	01-JUL-16	01-MAY-16
480	APPROVED	01-AUG-16	01-MAY-16
490	REJECTED	01-SEP-16	01-MAY-16

To achieve this the LAST_VALUE analytic function with the IGNORE NULLS addition is very handy as it allows to ignore those rows that we are not interested in (the approved ones).

Solution:

In a first step we nullify all rows in the generated last_rejected column that are of no interest:

DECODE(code_status,'REJECTED',date_app)

The next step searches the last entry in the result set where the column value of the generated column is not null.

LAST_VALUE (DECODE(code_status,'REJECTED',date_app) IGNORE NULLS) 
      OVER (ORDER BY skp_id)

And the last step makes sure, that we only evaluate to prior of the current row but not including the current row.

ROWS BETWEEN UNBOUNDED PRECEDING 
                            AND 1 PRECEDING

The whole statement looks like this:

WITH data (skp_id, code_status, date_app) 
       AS (SELECT 111, 'REJECTED', DATE '2016-01-01' FROM dual UNION ALL
           SELECT 123, 'APPROVED', DATE '2016-02-01' FROM dual UNION ALL
           SELECT 201, 'APPROVED', DATE '2016-03-01' FROM dual UNION ALL
           SELECT 302, 'REJECTED', DATE '2016-04-01' FROM dual UNION ALL
           SELECT 403, 'REJECTED', DATE '2016-05-01' FROM dual UNION ALL
           SELECT 450, 'APPROVED', DATE '2016-06-01' FROM dual UNION ALL
           SELECT 475, 'APPROVED', DATE '2016-07-01' FROM dual UNION ALL
           SELECT 480, 'APPROVED', DATE '2016-08-01' FROM dual UNION ALL
           SELECT 490, 'REJECTED', DATE '2016-09-01' FROM dual) 
SELECT skp_id
     , code_status
     , date_app
     , LAST_VALUE (DECODE(code_status,'REJECTED',date_app) IGNORE NULLS) 
             OVER (ORDER BY skp_id 
                   ROWS BETWEEN UNBOUNDED PRECEDING 
                            AND 1 PRECEDING) as last_rejected
 FROM data
/

Analytic functions are not really new – the came with 8.1.6 – but still they do not get the attraction they deserve.

ORACLE 12.2 – long names

03 Monday Oct 2016

Posted by troroblog in ORACLE 12c Release 2, SQL

≈ 2 Comments

Tags

ORACLE 12c Release 2, SQL

ORACLE 12.2 enables us to name our objects with names up to 128 byte long. Even if this has been something we were looking forward to for a very long time there are still many things to consider before deciding to cross the old 30 byte border.

Are there places in your PL/SQL code where you placed some VARCHAR2(30) variables to hold oracle object names?
What is the size of the columns in your log tables that you use to store the name of the object you are logging information about?
What about the variables you use in your PL/SQL code to put your dynamic SQL statements together? Are those variables wide enough?

How about this one:

If you are using some dynamic SQL that evaluates a dynamic select list using SYS.DBMS_SQL.DESCRIBE_COLUMNS … then this is a point where you will have to do some changes…

EXAMPLE: Problems using DBMS_SQL

CREATE TABLE Table_with_a_name_that_is_so_much_longer_than_usual (
   column_with_a_very_long_name_as_it_is_possible_in_other_databases NUMBER(5)
  ,another_column_with_also_a_very_very_long_name                    VARCHAR2(100)
);

Table TABLE_WITH_A_NAME_THAT_IS_SO_MUCH_LONGER_THAN_USUAL created.

DECLARE
   l_handle         PLS_INTEGER := sys.dbms_sql.open_cursor();
   l_column_counter NUMBER;
   l_describe_table_to_describe_a_very_long_table sys.dbms_sql.desc_tab;
BEGIN
   sys.dbms_sql.parse(c             => l_handle
                     ,statement     => 'SELECT * FROM Table_with_a_name_that_is_so_much_longer_than_usual'
                     ,language_flag => sys.dbms_sql.NATIVE);

   sys.dbms_sql.describe_columns(c       => l_handle
                                ,col_cnt => l_column_counter 
                                ,desc_t  => l_describe_table_to_describe_a_very_long_table);
 
   FOR i IN 1..l_column_counter
   LOOP
      sys.dbms_output.put_line('Column Name : ' || l_describe_table_to_describe_a_very_long_table(i).col_name);
   END LOOP;
END;
/

Error report -
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.DBMS_SQL", line 2084
ORA-06512: at line 10
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause: An arithmetic, numeric, string, conversion, or constraint error
 occurred. For example, this error occurs if an attempt is made to
 assign the value NULL to a variable declared NOT NULL, or if an
 attempt is made to assign an integer larger than 99 to a variable
 declared NUMBER(2).
*Action: Change the data, how it is manipulated, or how it is declared so
 that values do not violate constraints.

The problem is, that desc_rec in DBMS_SQL is still limited to 30 character length for the column names of the result.

To support long names ORACLE has implemented a new function and new data structures in DBMS_SQL.

EXAMPLE: Solution

DECLARE
   l_handle         PLS_INTEGER := sys.dbms_sql.open_cursor();
   l_column_counter NUMBER;
   l_describe_table_to_describe_a_very_long_table sys.dbms_sql.desc_tab2;
BEGIN
   sys.dbms_sql.parse(c             => l_handle
                     ,statement     => 'SELECT * FROM Table_with_a_name_that_is_so_much_longer_than_usual'
                     ,language_flag => sys.dbms_sql.NATIVE);

   sys.dbms_sql.describe_columns2(c       => l_handle
                                 ,col_cnt => l_column_counter 
                                 ,desc_t  => l_describe_table_to_describe_a_very_long_table);
 
   FOR i IN 1..l_column_counter
   LOOP
      sys.dbms_output.put_line('Column Name : ' || l_describe_table_to_describe_a_very_long_table(i).col_name);
   END LOOP;
END;
/

Column Name : COLUMN_WITH_A_VERY_LONG_NAME_AS_IT_IS_POSSIBLE_IN_OTHER_DATABASES
Column Name : ANOTHER_COLUMN_WITH_ALSO_A_VERY_VERY_LONG_NAME

Conclusion

Having the possibility to give decent names to objects – e.g. naming an index addresses_employees_usages_fk_index – instead of searching for abbreviation that are at least a little bit meaningfull is a nice thing. But before starting to cross the 30 byte border you have to deeply inspect your code.

ORACLE 12.2 -Data conversion enhancements

30 Friday Sep 2016

Posted by troroblog in ORACLE, ORACLE 12c Release 2, SQL, Tipps and Tricks

≈ Leave a comment

Tags

CAST, Data Conversion, ORACLE 12c Release 2, SQL, TO_CHAR, TO_DATE, TO_NUMBER

ORACLE 12.2 has a new option to handle conversion errors when using functions like CAST, TO_NUMBER, TO_CHAR, TO_DATE, etc. Using this option we may avoid conversion errors an return a default value instead.

The examples use TO_DATE but the option is useable in the other conversion functions accordingly

EXAMPLE TO_DATE

In case of a string to date conversion error we would like to return the 1st of January of 2016.

WITH data (str) AS (SELECT '15.01.2016' FROM dual UNION ALL
                    SELECT '30.06.2016' FROM dual UNION ALL
                    SELECT '30.02.2016' FROM dual UNION ALL
                    SELECT 'gugus' FROM dual UNION ALL
                    SELECT '07.03.2016' FROM dual UNION ALL
                    SELECT '15.10.2016' FROM dual)
SELECT str
 , TO_DATE(str DEFAULT '01.01.2016' ON CONVERSION ERROR,'DD.MM.YYYY') AS conversion
 FROM data
/

STR CONVERTED 
---------- ----------
15.01.2016 15.01.2016
30.06.2016 30.06.2016
30.02.2016 01.01.2016
gugus      01.01.2016
07.03.2016 07.03.2016
15.10.2016 15.10.2016

6 rows selected.

Using SYSDATE as default

Using SYSDATE as the default value instead of a literal value works as expected.

WITH data (str) AS (SELECT '15.01.2016' FROM dual UNION ALL
                    SELECT '30.06.2016' FROM dual UNION ALL
                    SELECT '30.02.2016' FROM dual UNION ALL
                    SELECT 'gugus' FROM dual UNION ALL
                    SELECT '07.03.2016' FROM dual UNION ALL
                    SELECT '15.10.2016' FROM dual)
SELECT str
 , TO_DATE(str DEFAULT SYSDATE ON CONVERSION ERROR,'DD.MM.YYYY') AS converted
 FROM data
/ 

STR CONVERTED 
---------- ----------
15.01.2016 15.01.2016
30.06.2016 30.06.2016
30.02.2016 30.09.2016
gugus      30.09.2016
07.03.2016 07.03.2016
15.10.2016 15.10.2016

6 rows selected.

Default to start of the year…

So next step would be to invoke a function on sysdate, to get the first day of the current year as the default value for an occuring conversion error.

WITH data (str) AS (SELECT '15.01.2016' FROM dual UNION ALL
                    SELECT '30.06.2016' FROM dual UNION ALL
                    SELECT '30.02.2016' FROM dual UNION ALL
                    SELECT 'gugus' FROM dual UNION ALL
                    SELECT '07.03.2016' FROM dual UNION ALL
                    SELECT '15.10.2016' FROM dual)
 SELECT str
      , TO_DATE(str DEFAULT TRUNC(SYSDATE,'YEAR') ON CONVERSION ERROR,'DD.MM.YYYY') AS converted
 FROM data
/ 

Error at Command Line : 8 Column : 28
 Error report -
 SQL Error: ORA-43907: This argument must be a literal or bind variable.

So, I am a little bit puzzled…we can use SYSDATE but not having a function around it…

Choosing another column value as the default

Now, how about choosing another column of the same table as the default value?

WITH data (str,dateval) AS (SELECT '15.01.2016', date '2015-07-15' FROM dual UNION ALL
                            SELECT '30.06.2016', date '2015-07-16' FROM dual UNION ALL
                            SELECT '30.02.2016', date '2015-07-17' FROM dual UNION ALL
                            SELECT 'gugus' , date '2015-07-18' FROM dual UNION ALL
                            SELECT '07.03.2016', date '2015-07-19' FROM dual UNION ALL
                            SELECT '15.10.2016', date '2015-07-20' FROM dual)
 SELECT str
      , dateval
      , TO_DATE(str DEFAULT dateval ON CONVERSION ERROR,'DD.MM.YYYY') AS converted
 FROM data
/
 
Error at Command Line : 9 Column : 29
Error report -
SQL Error: ORA-43907: This argument must be a literal or bind variable.

Ok, perhaps it needs to be a string using the same format mask?

WITH data (str,dateval) AS (SELECT '15.01.2016', date '2015-07-15' FROM dual UNION ALL
                            SELECT '30.06.2016', date '2015-07-16' FROM dual UNION ALL
                            SELECT '30.02.2016', date '2015-07-17' FROM dual UNION ALL
                            SELECT 'gugus' , date '2015-07-18' FROM dual UNION ALL
                            SELECT '07.03.2016', date '2015-07-19' FROM dual UNION ALL
                            SELECT '15.10.2016', date '2015-07-20' FROM dual)
 SELECT str
      , dateval
      , TO_DATE(str DEFAULT TO_CHAR(dateval,'DD.MM.YYYY') ON CONVERSION ERROR,'DD.MM.YYYY') AS converted
 FROM data
/
 
Error at Command Line : 9 Column : 29
Error report -
SQL Error: ORA-43907: This argument must be a literal or bind variable.

So, how can this be solved?

Now, we could return NULL in case of a conversion error and then replace a null value by the value of the second column…like this:

WITH data (str,dateval) AS (SELECT '15.01.2016', date '2015-07-15' FROM dual UNION ALL
                            SELECT '30.06.2016', date '2015-07-16' FROM dual UNION ALL
                            SELECT '30.02.2016', date '2015-07-17' FROM dual UNION ALL
                            SELECT 'gugus'     , date '2015-07-18' FROM dual UNION ALL
                            SELECT '07.03.2016', date '2015-07-19' FROM dual UNION ALL
                            SELECT '15.10.2016', date '2015-07-20' FROM dual UNION ALL
                            SELECT NULL        , date '2015-07-21' FROM DUAL)
SELECT str
     , dateval
     , NVL(TO_DATE(str DEFAULT NULL ON CONVERSION ERROR,'DD.MM.YYYY'),dateval) AS converted
 FROM data
/

STR        DATEVAL    CONVERTED 
---------- ---------- ----------
15.01.2016 15.07.2015 15.01.2016
30.06.2016 16.07.2015 30.06.2016
30.02.2016 17.07.2015 17.07.2015
gugus      18.07.2015 18.07.2015
07.03.2016 19.07.2015 07.03.2016
15.10.2016 20.07.2015 15.10.2016
           21.07.2015 21.07.2015

7 rows selected.

but…I only wanted to replace values that are not convertable by the second value, not null values…

WITH data (str,dateval) AS (SELECT '15.01.2016', date '2015-07-15' FROM dual UNION ALL
                            SELECT '30.06.2016', date '2015-07-16' FROM dual UNION ALL
                            SELECT '30.02.2016', date '2015-07-17' FROM dual UNION ALL
                            SELECT 'gugus' , date '2015-07-18' FROM dual UNION ALL
                            SELECT '07.03.2016', date '2015-07-19' FROM dual UNION ALL
                            SELECT '15.10.2016', date '2015-07-20' FROM dual UNION ALL
                            SELECT NULL , date '2015-07-21' FROM DUAL)
SELECT str
     , dateval
     , CASE 
          WHEN str IS NULL THEN NULL
          ELSE NVL(TO_DATE(str DEFAULT NULL ON CONVERSION ERROR,'DD.MM.YYYY'),dateval)
       END AS converted
 FROM data
/

A little bit complicated, but this way I get what I want.

Handling different date formats in one query

Now another test case, having an input stream using different date formats…

WITH data (str) AS (SELECT '15.03.2016' FROM dual UNION ALL
                    SELECT '2005/03/15' FROM dual UNION ALL
                    SELECT '15-MRZ-2016' FROM dual UNION ALL
                    SELECT '15-MAR-2016' FROM dual UNION ALL
                    SELECT '15. MARCH 2016' FROM dual UNION ALL
                    SELECT '15-MAALIS-2016' FROM DUAL UNION ALL
                    SELECT '15-BRE-2016' FROM DUAL)
SELECT str
     , COALESCE(TO_DATE(str DEFAULT NULL ON CONVERSION ERROR,'DD.MM.YYYY')
               ,TO_DATE(str DEFAULT NULL ON CONVERSION ERROR,'YYYY/MM/DD')
               ,TO_DATE(str DEFAULT NULL ON CONVERSION ERROR,'DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH')
               ,TO_DATE(str DEFAULT NULL ON CONVERSION ERROR,'DD-MON-YYYY','NLS_DATE_LANGUAGE=GERMAN')
               ,TO_DATE(str DEFAULT NULL ON CONVERSION ERROR,'DD-MON-YYYY','NLS_DATE_LANGUAGE=FINNISH')
       ) AS converted
 FROM data
/ 

STR            CONVERTED 
-------------- ----------
15.03.2016     15.03.2016
2005/03/15     15.03.2005
15-MRZ-2016    15.03.2016
15-MAR-2016    15.03.2016
15. MARCH 2016 15.03.2016
15-MAALIS-2016 15.03.2016
15-BRE-2016 

7 rows selected.

Conclusion

The DEFAULT … ON CONVERSION ERROR is a very powerful feature, which will help us solving conversion errors very elegant. Of course we will still not be able to do conversions when the input value is not unambiguous (A string value of ‘03.05.2016’ could be the 3rd of May as well as the 5th of March) but still a very welcome feature.

ORACLE 12.2 – LISTAGG Enhancements

30 Friday Sep 2016

Posted by troroblog in ORACLE, ORACLE 12c Release 2, SQL

≈ Leave a comment

Tags

LISTAGG, New Feature, ORACLE 12c Release 2, SQL

As in earlier versions LISTAGG still returns a VARCHAR2 datatype with the known limitations (4 or 32 K). But instead of ending in an error on a size overflow we now have the chance to tell the function to truncate the return string in case it gets to long.

The syntax has changed slightly between the beta version of ORACLE 12.2 and the productive one, there some changes had to be done to this post.

LISTAGG default behaviour

The default behaviour is still the same resulting in an Oracle error on size overflow.

WITH test (id, str) as (SELECT MOD(rownum,7), RPAD('Text',10,'X') 
 FROM dual
 CONNECT BY ROWNUM < 10000)
SELECT id, LISTAGG(str,'-') WITHIN GROUP(ORDER BY 1) AS string_created
 FROM test 
 GROUP BY id
/ 

ORA-01489: result of string concatenation is too long

ON OVERFLOW TRUNCATE

The new option on overflow truncated leeds to a trunctated string having three dots at the end followed by the number of truncated characters in brackets.

The result lists only the end of the strings created.

WITH test (id, str) as (SELECT MOD(rownum,7), RPAD('Text',10,'X')
 FROM dual
 CONNECT BY ROWNUM < 10000)
SELECT id
     , LISTAGG(str,'-' ON OVERFLOW TRUNCATE) WITHIN GROUP(ORDER BY 1) AS string_created
 FROM test 
 GROUP BY id
/ 

ID STRING_CREATED
-- ------------------------------------------
 0 TextXXXXXX-TextXXXXXX-TextXXXXXX-...(1068)
 1 TextXXXXXX-TextXXXXXX-TextXXXXXX-...(1069)
 2 TextXXXXXX-TextXXXXXX-TextXXXXXX-...(1069)

Define overflow characters

We are free to define the text that should be shown as the overflow indicator. The text we want to see in case of an overflow is defined following the “ON OVERFLOW TRUNCATE” option.

WITH test (id, str) as (SELECT MOD(rownum,7), RPAD('Text',10,'X')
 FROM dual
 CONNECT BY ROWNUM < 10000)
SELECT id
     , LISTAGG(str,'-' ON OVERFLOW TRUNCATE ' [more] ') WITHIN GROUP(ORDER BY 1) AS string_created
 FROM test 
 GROUP BY id
/ 

ID STRING_CREATED
-- -----------------------------------------------
 0 TextXXXXXX-TextXXXXXX-TextXXXXXX- [more] (1068)
 1 TextXXXXXX-TextXXXXXX-TextXXXXXX- [more] (1069)
 2 TextXXXXXX-TextXXXXXX-TextXXXXXX- [more] (1069)

Omit the counting

The number of truncated characters can be omitted by adding WITHOUT COUNT (default is with count).

WITH test (id, str) as (SELECT MOD(rownum,7), RPAD('Text',10,'X')
 FROM dual
 CONNECT BY ROWNUM < 10000)
SELECT id
     , LISTAGG(str,'-' ON OVERFLOW TRUNCATE ' [more] ' WITHOUT COUNT) WITHIN GROUP(ORDER BY 1) AS string_created
 FROM test 
 GROUP BY id
/ 

ID STRING_CREATED
-- -----------------------------------------------
 0 TextXXXXXX-TextXXXXXX-TextXXXXXX- [more] 
 1 TextXXXXXX-TextXXXXXX-TextXXXXXX- [more] 
 2 TextXXXXXX-TextXXXXXX-TextXXXXXX- [more]

Conclusion

Truncating the result of a LISTAGG function to avoid the overflow error is a pretty cool feature of Oracle 12.2 SQL. If you need to concatenate strings beyond the size limit of the VARCHAR2 datatype you will still need to write your own CLOB aggregate function but if it is enough to know, that there was more in the string before having concatenated it the on overflow truncate is really handy.

Grouping and counting continuity

15 Tuesday Dec 2015

Posted by troroblog in ORACLE, ORACLE 12c Release 1, SQL, Tipps and Tricks

≈ Leave a comment

Tags

Analytic Functions, Model Clause, ORACLE, ORACLE 12c Release 1, Row Pattern Matching, SQL

A colleague of mine had the problem to group continuous entries of same type to be able to count number of continuos entries.

His Data:

TS        USERNAME RETURNCODE
--------- -------- ----------
05-NOV-15 SCOTT         28000
04-NOV-15 SCOTT         28000
03-NOV-15 SCOTT          1027 
02-NOV-15 SCOTT          1027
01-NOV-15 SCOTT          1027
31-OCT-15 SCOTT          1027
30-OCT-15 SCOTT             0
29-OCT-15 SCOTT          1027
28-OCT-15 SCOTT          1027
27-OCT-15 SCOTT             0
26-OCT-15 SCOTT             0
25-OCT-15 SCOTT          1027

Output needed:

TS        USERNAME RETURNCODE GROUP COUNT
--------  -------- ---------- ----- -----
05-NOV-15 SCOTT         28000     1     2
04-NOV-15 SCOTT         28000     1     2
03-NOV-15 SCOTT          1027     2     4
02-NOV-15 SCOTT          1027     2     4
01-NOV-15 SCOTT          1027     2     4
31-OCT-15 SCOTT          1027     2     4
30-OCT-15 SCOTT             0     3     1
29-OCT-15 SCOTT          1027     4     2
28-OCT-15 SCOTT          1027     4     2
27-OCT-15 SCOTT             0     5     2
26-OCT-15 SCOTT             0     5     2
25-OCT-15 SCOTT          1027     6     1

So basically the problem was to build groups on continuous identical returncodes and increase group number with every change. After those groups have been built the count column can be calculated using a COUNT(*) OVER (PARTITION BY the group-identification).

Solution 1 – ANALYTIC FUNCTION:

This solution compares the returncode value of the previous row (within same username ordered by timestamp) with the current value and returns “1” in case of a difference if the current value is unchanged NULL is returned. In the second step a running summary over this 1/NULL result is built.

WITH data (ts, username, returncode)        
       AS ( SELECT DATE '2015-11-05', 'SCOTT', 28000 FROM dual UNION ALL
            SELECT DATE '2015-11-04', 'SCOTT', 28000 FROM dual UNION ALL
            SELECT DATE '2015-11-03', 'SCOTT', 1027  FROM dual UNION ALL
            SELECT DATE '2015-11-02', 'SCOTT', 1027  FROM dual UNION ALL
            SELECT DATE '2015-11-01', 'SCOTT', 1027  FROM dual UNION ALL 
            SELECT DATE '2015-10-31', 'SCOTT', 1027  FROM dual UNION ALL
            SELECT DATE '2015-10-30', 'SCOTT', 0     FROM dual UNION ALL
            SELECT DATE '2015-10-29', 'SCOTT', 1027  FROM dual UNION ALL
            SELECT DATE '2015-10-28', 'SCOTT', 1027  FROM dual UNION ALL
            SELECT DATE '2015-10-27', 'SCOTT', 0     FROM dual UNION ALL
            SELECT DATE '2015-10-26', 'SCOTT', 0     FROM dual UNION ALL
            SELECT DATE '2015-10-25', 'SCOTT', 1027  FROM dual) 
SELECT ts
      ,username
      ,returncode
      ,SUM(start_entry) OVER (PARTITION BY username ORDER BY ts DESC) AS grp
  FROM (SELECT ts
             , username
             , returncode
             , CASE
                  WHEN returncode = LAG(returncode) OVER (PARTITION BY username
                                                              ORDER BY ts DESC)
                     THEN 0                   
                  ELSE 1
               END AS start_entry 
          FROM data) 
/

TS        USERN RETURNCODE GRP
--------- ----- ---------- ----------
05-NOV-15 SCOTT      28000          1
04-NOV-15 SCOTT      28000          1
03-NOV-15 SCOTT       1027          2
02-NOV-15 SCOTT       1027          2
01-NOV-15 SCOTT       1027          2
31-OCT-15 SCOTT       1027          2
30-OCT-15 SCOTT          0          3
29-OCT-15 SCOTT       1027          4
28-OCT-15 SCOTT       1027          4
27-OCT-15 SCOTT          0          5
26-OCT-15 SCOTT          0          5
25-OCT-15 SCOTT       1027          6

Solution 2 – MODEL clause:

The model clauses has the possibility to compare the values of different rows and generate a value for a different column based on this comparision. In our case I compare the value of the returncode of the previous row to the value of the returncode of the current row. In case of identical values i reuse the value of the grp column of the previous row to set the grp value of the current row, in case of a difference I increase the previous rows value by 1.

WITH DATA (ts, username, returncode)
        AS( SELECT DATE '2015-11-05', 'SCOTT', 28000 FROM dual UNION ALL
            SELECT DATE '2015-11-04', 'SCOTT', 28000 FROM dual UNION ALL 
            SELECT DATE '2015-11-03', 'SCOTT', 1027  FROM dual UNION ALL
            SELECT DATE '2015-11-02', 'SCOTT', 1027  FROM dual UNION ALL
            SELECT DATE '2015-11-01', 'SCOTT', 1027  FROM dual UNION ALL
            SELECT DATE '2015-10-31', 'SCOTT', 1027  FROM dual UNION ALL
            SELECT DATE '2015-10-30', 'SCOTT', 0     FROM dual UNION ALL
            SELECT DATE '2015-10-29', 'SCOTT', 1027  FROM dual UNION ALL
            SELECT DATE '2015-10-28', 'SCOTT', 1027  FROM dual UNION ALL
            SELECT DATE '2015-10-27', 'SCOTT', 0     FROM dual UNION ALL
            SELECT DATE '2015-10-26', 'SCOTT', 0     FROM dual UNION ALL
            SELECT DATE '2015-10-25', 'SCOTT', 1027  FROM dual)
SELECT ts, username, returncode, grp
  FROM data
      MODEL PARTITION BY (username)
            DIMENSION BY (ROW_NUMBER() OVER (PARTITION BY username 
                                                  ORDER BY ts) AS rn)
            MEASURES (1 as grp,returncode,ts)
            RULES AUTOMATIC ORDER
            (grp[rn > 1]  = CASE WHEN returncode[CV()] = returncode[cv()-1] 
                                 THEN grp[CV()-1]
                                 ELSE grp[CV()-1] + 1
                            END)
/

Solution 3 “MATCH_RECOGNIZE (12c)”:

With the MATCH_RECOGNIZE clause we can do a count of the group members simultaneously to the group assignement.

WITH DATA (ts, username, returncode)
        AS(SELECT DATE '2015-11-05', 'SCOTT', 28000 FROM dual UNION ALL
           SELECT DATE '2015-11-04', 'SCOTT', 28000 FROM dual UNION ALL 
           SELECT DATE '2015-11-03', 'SCOTT', 1027  FROM dual UNION ALL
           SELECT DATE '2015-11-02', 'SCOTT', 1027  FROM dual UNION ALL
           SELECT DATE '2015-11-01', 'SCOTT', 1027  FROM dual UNION ALL
           SELECT DATE '2015-10-31', 'SCOTT', 1027  FROM dual UNION ALL
           SELECT DATE '2015-10-30', 'SCOTT', 0     FROM dual UNION ALL
           SELECT DATE '2015-10-29', 'SCOTT', 1027  FROM dual UNION ALL
           SELECT DATE '2015-10-28', 'SCOTT', 1027  FROM dual UNION ALL
           SELECT DATE '2015-10-27', 'SCOTT', 0     FROM dual UNION ALL
           SELECT DATE '2015-10-26', 'SCOTT', 0     FROM dual UNION ALL
           SELECT DATE '2015-10-25', 'SCOTT', 1027  FROM dual)
SELECT ts, username, returncode, grp, continuous_rows
  FROM DATA
  MATCH_RECOGNIZE (PARTITION BY username
                   ORDER BY ts DESC                    
                   MEASURES FINAL COUNT(*) AS continuous_rows
                           ,MATCH_NUMBER() AS grp
                   ALL ROWS PER MATCH
                   AFTER MATCH SKIP PAST LAST ROW
                   PATTERN (strt cont*)
                   DEFINE cont AS cont.returncode = PREV(cont.returncode)) x
/

Gap Detection – Tabibitosan Method

01 Wednesday Jul 2015

Posted by troroblog in ORACLE, SQL, Tipps and Tricks

≈ Leave a comment

Tags

ORACLE, SQL, TABIBITOSAN

Another question from the ORACLE SQL & PL/SQL Forum.

Problem:

The original poster (OP) needed to find the first “missing” entry in a series of entries.

Entries are like:

Set 1: 0001, 0004, 0006 First missing entry: 0002
Set 2: 0001, 0002, 0006 First missing entry: 0003
Set 3: 0001, 0002, 0003 First missing entry: 0004
Set 4: 0002, 0003, 0004 First missing entry: 0001

Test Data:

WITH data (id,entry) AS (SELECT 1, '0001' FROM dual UNION ALL
                         SELECT 1, '0004' FROM dual UNION ALL
                         SELECT 1, '0006' FROM dual UNION ALL
                         SELECT 2, '0001' FROM dual UNION ALL
                         SELECT 2, '0002' FROM dual UNION ALL
                         SELECT 2, '0006' FROM dual UNION ALL
                         SELECT 3, '0001' FROM dual UNION ALL
                         SELECT 3, '0002' FROM dual UNION ALL
                         SELECT 3, '0003' FROM dual UNION ALL
                         SELECT 4, '0002' FROM dual UNION ALL
                         SELECT 4, '0003' FROM dual UNION ALL
                         SELECT 4, '0004' FROM dual)
SELECT id, entry
  FROM data
/

        ID ENTRY
---------- -----
         1 0001 
         1 0004 
         1 0006 
         2 0001 
         2 0002 
         2 0006 
         3 0001 
         3 0002 
         3 0003 
         4 0002 
         4 0003 
         4 0004 

 12 rows selected

Solution:

So first step would be to calculate the effective entry and the entry – “value an entry had if everything was gapless” (the column is called distance).

WITH data (id,entry) AS (SELECT 1, '0001' FROM dual UNION ALL
                         SELECT 1, '0004' FROM dual UNION ALL
                         SELECT 1, '0006' FROM dual UNION ALL
                         SELECT 2, '0001' FROM dual UNION ALL
                         SELECT 2, '0002' FROM dual UNION ALL
                         SELECT 2, '0006' FROM dual UNION ALL
                         SELECT 3, '0001' FROM dual UNION ALL
                         SELECT 3, '0002' FROM dual UNION ALL
                         SELECT 3, '0003' FROM dual UNION ALL
                         SELECT 4, '0002' FROM dual UNION ALL
                         SELECT 4, '0003' FROM dual UNION ALL
                         SELECT 4, '0004' FROM dual)
SELECT id, entry
     , TO_NUMBER(entry) - ROW_NUMBER() OVER (PARTITION BY id 
                                             ORDER BY entry) AS distance
  FROM data
/

        ID ENTRY   DISTANCE
---------- ----- ----------
         1 0001           0
         1 0004           2
         1 0006           3
         2 0001           0
         2 0002           0
         2 0006           3
         3 0001           0
         3 0002           0
         3 0003           0
         4 0002           1
         4 0003           1
         4 0004           1

12 rows selected

As long as the distance is 0 the entry belongs to a gapless series. To find the first missing entry, we are looking for the last entry with a distance of 0.

WITH data (id,entry) AS (SELECT 1, '0001' FROM dual UNION ALL
                         SELECT 1, '0004' FROM dual UNION ALL
                         SELECT 1, '0006' FROM dual UNION ALL
                         SELECT 2, '0001' FROM dual UNION ALL
                         SELECT 2, '0002' FROM dual UNION ALL
                         SELECT 2, '0006' FROM dual UNION ALL
                         SELECT 3, '0001' FROM dual UNION ALL
                         SELECT 3, '0002' FROM dual UNION ALL
                         SELECT 3, '0003' FROM dual UNION ALL
                         SELECT 4, '0002' FROM dual UNION ALL
                         SELECT 4, '0003' FROM dual UNION ALL
                         SELECT 4, '0004' FROM dual)
SELECT id, MAX(DECODE(distance,0,id,NULL)) AS last_in_serie
  FROM (SELECT id, entry
             , TO_NUMBER(entry) - ROW_NUMBER() OVER (PARTITION BY id
                                                         ORDER BY entry) AS distance
          FROM data)
  GROUP BY id
/

        ID LAST_IN_SERIE
---------- -------------
         1             1
         2             2
         3             3
         4

By increasing this last_in_series value by 1 we get the first missing entry.

WITH data (id,entry) AS (SELECT 1, '0001' FROM dual UNION ALL
                         SELECT 1, '0004' FROM dual UNION ALL
                         SELECT 1, '0006' FROM dual UNION ALL
                         SELECT 2, '0001' FROM dual UNION ALL
                         SELECT 2, '0002' FROM dual UNION ALL
                         SELECT 2, '0006' FROM dual UNION ALL
                         SELECT 3, '0001' FROM dual UNION ALL
                         SELECT 3, '0002' FROM dual UNION ALL
                         SELECT 3, '0003' FROM dual UNION ALL
                         SELECT 4, '0002' FROM dual UNION ALL
                         SELECT 4, '0003' FROM dual UNION ALL
                         SELECT 4, '0004' FROM dual)
SELECT id, TO_CHAR(NVL(MAX(DECODE(distance,0,id,NULL)),0) + 1,'FM0000') as next_entry
  FROM (SELECT id, entry
             , TO_NUMBER(entry) - ROW_NUMBER() OVER (PARTITION BY id
                                                         ORDER BY entry) AS distance
          FROM data)
  GROUP BY id
/

        ID NEXT_ENTRY
---------- ----------
         1 0002      
         2 0003      
         3 0004      
         4 0001

Partitioned Outer Join

16 Tuesday Jun 2015

Posted by troroblog in ORACLE, SQL, Tipps and Tricks

≈ Leave a comment

Tags

ORACLE, OUTER JOIN, SQL

This posts describes a solution to a problem posted on the ORACLE SQL and PL/SQL forums.

Problem:

The original poster (OP) has a table which has monthly data for agents.

WITH agt_dum(agent_id,mon,commission)
         AS (SELECT 1, 1, 50  FROM dual UNION ALL
             SELECT 1, 2, 100 FROM dual UNION ALL
             SELECT 1, 6, 25  FROM dual UNION ALL
             SELECT 2, 1, 10  FROM dual UNION ALL
             SELECT 2, 2, 20  FROM dual)
SELECT *
  FROM agt_dum
/

  AGENT_ID        MON COMMISSION
---------- ---------- ----------
         1          1         50
         1          2        100
         1          6         25
         2          1         10
         2          2         20

as you can see not every month has data for every AGENT_ID and the OP needed to have a query result like:

  AGENT_ID        MON COMMISSION
---------- ---------- ----------
         1          1         50
         1          2        100
         1          3          0
         1          4          0
         1          5          0
         1          6         25
         1          7          0
         1          8          0
         1          9          0
         1         10          0
         1         11          0
         1         12          0
         2          1         10
         2          2         20
         2          3          0
         2          4          0
         2          5          0
         2          6          0
         2          7          0
         2          8          0
         2          9          0
         2         10          0
         2         11          0
         2         12          0

meaning for each month an entry for each AGENT_ID and if no commission exists (no row exist) a 0 value for that specific column.

Solution(s):

The first thing that I though about was to have an outer join on the AGENT_ID. The driving table would be something that has all months (month numbers) from 1 to 12.

Outer Join with a “ALL_MONTHS” source:

WITH all_months (mon) 
             AS (SELECT rownum AS mon
                   FROM dual
                CONNECT BY rownum <= 12)
SELECT mon
  FROM all_months
/ 

MON
---------
        1
        2
        3
        4
        5
        6
        7
        8
        9
       10
       11
       12

This source could be used as the driving table in an outer join query

WITH agt_dum(agent_id,mon,commission)
         AS (SELECT 1, 1, 50  FROM dual union all
             SELECT 1, 2, 100 FROM dual union all
             SELECT 1, 6, 25  FROM dual union all
             SELECT 2, 1, 10  FROM dual union all
             SELECT 2, 2, 20  FROM dual)
    ,all_months (mon) AS (SELECT ROWNUM
                            FROM dual 
                          CONNECT BY ROWNUM <= 12)
SELECT ad.agent_id, am.mon, NVL(ad.commission,0) AS commission
  FROM            all_months am
  LEFT OUTER JOIN agt_dum    ad ON (ad.mon = am.mon)
 ORDER by ad.agent_id, am.mon
/

But a simple outer join will no solve the problem, as all the outer joined columns will be NULL…

  AGENT_ID        MON COMMISSION
---------- ---------- ----------
         1          1         50
         1          2        100
         1          6         25
         2          1         10
         2          2         20
                    3          0
                    4          0
                    5          0
                    7          0
                    8          0
                    9          0
                   10          0
                   11          0
                   12          0

As you can see you get an “empty” row for all months that are not available with any AGENT_ID … but what OP wanted was to have one row for every month for every AGENT_ID.

Partitioned Outer Join

So the solution for that is having a partitioned outer join like:

WITH agt_dum(agent_id,mon,commission)
         AS (SELECT 1, 1, 50  FROM dual union all
             SELECT 1, 2, 100 FROM dual union all
             SELECT 1, 6, 25  FROM dual union all
             SELECT 2, 1, 10  FROM dual union all
             SELECT 2, 2, 20  FROM dual)
    ,all_months (mon) AS (SELECT ROWNUM
                            FROM dual 
                          CONNECT BY ROWNUM <= 12)
SELECT ad.agent_id, am.mon, NVL(ad.commission,0) AS commission
  FROM            all_months am
  LEFT OUTER JOIN agt_dum    ad PARTITION BY (ad.agent_id) ON (ad.mon = am.mon)
 ORDER by ad.agent_id, am.mon
/

Using this query, ORACLE does an OUTER JOIN for every AGENT_ID in the AGT_DUM view and the result is exactly what OP wanted.

  AGENT_ID        MON COMMISSION
---------- ---------- ----------
         1          1         50
         1          2        100
         1          3          0
         1          4          0
         1          5          0
         1          6         25
         1          7          0
         1          8          0
         1          9          0
         1         10          0
         1         11          0
         1         12          0
         2          1         10
         2          2         20
         2          3          0
         2          4          0
         2          5          0
         2          6          0
         2          7          0
         2          8          0
         2          9          0
         2         10          0
         2         11          0
         2         12          0

Additional solutions:

Model Clause:

WITH agt_dum(agent_id,mon,commission)         
         AS (SELECT 1, 1, 50  FROM dual UNION ALL             
             SELECT 1, 2, 100 FROM dual UNION ALL             
             SELECT 1, 6, 25  FROM dual UNION ALL             
             SELECT 2, 1, 10  FROM dual UNION ALL             
             SELECT 2, 2, 20  FROM dual)
select agent_id  
     , mon  
     , commission  
 FROM agt_dum  
 MODEL PARTITION BY (agent_id)  
       DIMENSION BY (mon)  
       MEASURES (commission)   
       RULES ITERATE (12)  
       (  
           commission[ITERATION_NUMBER + 1] = NVL(commission[cv()], 0)  
       )  
 ORDER BY agent_id  
        , mon;

ORACLE 12c Release 1 – Session Level Sequences

18 Wednesday Sep 2013

Posted by troroblog in ORACLE, ORACLE 11g Release 2, SQL

≈ Leave a comment

Tags

ORACLE, ORACLE 12c Release 1, Sequence, SQL

A sequence that starts at the starting point with every session that accesses the sequence…what is this good for?

Now, I could think at things like logging, where we could give every step of a session a ascending step_id, which would be stored in the log table along with the session_id to which the step belonged. Or we could have a transaction which creates a series of entries in the database and within the transaction those entries are numbered for whatever purpose. Generating Primary Keys for a Global Temporary Table would be another field where those kinds of sequences could be used.

Create a session level sequence

A session level sequence is generated using the new SESSION key word. This will restart the sequence for every session.

CREATE SEQUENCE session_seq
   START WITH   1
   INCREMENT BY 1
   SESSION
/

Usage

Example filling a global temporary table using a session level sequence as primary key generator:

CREATE SEQUENCE session_seq
 START WITH   1
 INCREMENT BY 1
 SESSION
/

Sequence created.

CREATE GLOBAL TEMPORARY TABLE employees_temp_table (
    employee_id  NUMBER DEFAULT session_seq.nextval NOT NULL
   ,first_name VARCHAR2(20)
   ,last_name VARCHAR2(25)
   )
   ON COMMIT PRESERVE ROWS
/

Table created.

REM ===========================================================================
REM Add rows to table
REM ===========================================================================
INSERT INTO employees_temp_table (first_name, last_name)
SELECT first_name, last_name
  FROM hr.employees
 WHERE rownum < 20
/

19 rows created.

REM ===========================================================================
REM Show the results
REM ===========================================================================
SELECT *
  FROM employees_temp_table
/

EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
          1 Ellen                Abel
          2 Sundar               Ande
          3 Mozhe                Atkinson
          4 David                Austin
          5 Hermann              Baer
          6 Shelli               Baida
          7 Amit                 Banda
          8 Elizabeth            Bates
          9 Sarah                Bell
         10 David                Bernstein
         11 Laura                Bissot
         12 Harrison             Bloom
         13 Alexis               Bull
         14 Anthony              Cabrio
         15 Gerald               Cambrault
         16 Nanette              Cambrault
         17 John                 Chen
         18 Kelly                Chung
         19 Karen                Colmenares

19 rows selected.

If another session does the same thing, the employee_id will start at 1 again.

Date arithmetic

03 Tuesday Sep 2013

Posted by troroblog in ORACLE, SQL, Tipps and Tricks

≈ Leave a comment

Tags

DATE, ORACLE, SQL

The hard way and the smart way

A problem that often arises in the classroom is how to calculate the difference (hours, minutes and seconds) of two DATE values.

The hard way

At various places on the web you may find solutions like this:

WITH example_data AS (SELECT TO_DATE('01.01.2013 13:35:17','dd.mm.yyyy hh24:mi:ss') AS date_from
                            ,TO_DATE('01.01.2013 17:22:08','dd.mm.yyyy hh24:mi:ss') AS date_to
                        FROM DUAL)
SELECT TO_CHAR(TRUNC((date_to - date_from) * 24),'FM00') || ':' 
    || TO_CHAR(TRUNC(((date_to - date_from) * 24 - TRUNC((date_to - date_from) * 24)) * 60),'FM00') || ':' 
    || TO_CHAR(((date_to - date_from) * 24 * 60 - TRUNC((date_to - date_from) * 24 * 60)) * 60,'FM00') AS time
  FROM example_data;

TIME
-----------
03:46:51

Personally I would call this the hard way to solve the problem, because there is a much easier solution for this problem.

The smart way

WITH example_data AS (SELECT TO_DATE('01.01.2013 13:35:17','dd.mm.yyyy hh24:mi:ss') AS date_from
                            ,TO_DATE('01.01.2013 17:22:08','dd.mm.yyyy hh24:mi:ss') AS date_to
                        FROM DUAL)
SELECT TO_CHAR(TRUNC(SYSDATE) + (date_to - date_from),'HH24:MI:SS') as TIME
  FROM example_data;

TIME
--------
03:46:51

This solution makes use of the date arithmetic logic of ORACLE where the difference between two dates values is expressed as a number representing the number of days including a fraction of days. Furthermore adding a number to a date again results in a date data type. So if we add the difference of two date values to midnight and use TO_CHAR to display the time component of the result, this result will express the difference of those two dates. Now what should we do, if the difference is more than one day?
We just could make use of the day in year format after having added the difference between the two date values to the 31st of december.

WITH example_data AS (SELECT TO_DATE('01.01.2013 13:35:17','dd.mm.yyyy hh24:mi:ss') AS date_from
                            ,TO_DATE('01.01.2013 17:22:08','dd.mm.yyyy hh24:mi:ss') AS date_to
                        FROM DUAL
                      UNION ALL
                      SELECT TO_DATE('01.01.2013 13:35:17','dd.mm.yyyy hh24:mi:ss') AS date_from
                            ,TO_DATE('03.01.2013 17:22:08','dd.mm.yyyy hh24:mi:ss') AS date_to
                        FROM DUAL)
SELECT CASE
          WHEN TRUNC(date_to - date_from) > 0
             THEN TO_CHAR((TRUNC(SYSDATE,'year')-1) + (date_to - date_from),'DDD HH24:MI:SS')
          ELSE TO_CHAR(TRUNC(SYSDATE) + (date_to - date_from),'HH24:MI:SS')
       END as TIME
  FROM example_data;

TIME
------------
03:46:51
002 03:46:51

This solution represents in my opinion the smart way to calculate time difference between two date values.