Tag Archives: ORACLE

ORACLE 18c – TO_UTC_TIMESTAMP_TZ

29 Saturday Feb 2020

Posted by in BUG, ORACLE, ORACLE 18c, SQL

≈ Leave a comment

Tags

,

TO_UTC_TIMESTAMP_TZ takes a ISO8601 formatted date string and converts it to a TIMESTAMP WITH TIMEZONE datatype.

According to documentation this is the ebnf of to_utc_timestamp_tc

to_utc_timestamp_tz

The varchar parameter may be either a date (only) or a date with time combination. The format of the parameter is:

Date only:

YYYY-MM-DD

Date / time:

YYYY-MM-DDThh:mm:ss[.s[s[s[s[s[s]]]]]][Z|(+|-)hh:mm]

Example

with data (d) as (select '2020-02-28T21:32:52'       from dual union all
                  select '2020-02-28T21:32:52+02:00' from dual union all
                  select '2020-02-28T19:32:52Z'      from dual union all
                  select '2020-02-28T19:32:52+00:00' from dual union all
                  select '2020-02-28T21:32:52.12679' from dual union all
                  select '2020-02-28')
select to_utc_timestamp_tz(d) as converted
  from data;

CONVERTED 
---------------------------------------------
28.02.2020 21:32:52.000000000 GMT
28.02.2020 21:32:52.000000000 +02:00
28.02.2020 19:32:52.000000000 GMT
28.02.2020 19:32:52.000000000 GMT
28.02.2020 21:32:52.126790000 GMT
28.02.2020 00:00:00.000000000 GMT

6 rows selected.

DEFAULT RETURN VALUE ON CONVERSION ERROR

As most of the other conversion functions documentation says that TO_UTC_TIMESTAMP_TZ supports the on conversion error clause too. But when trying to use it you will get an error.

with data (d) as (select '2018-10-26T21:32:52'       from dual union all
                  select '2018-10-26T21:32:52+02:00' from dual union all
                  select '2018-10-26T19:32:52Z'      from dual union all
                  select '2018-10-26T19:32:52+00:00' from dual union all
                  select '2018-10-26T21:32:52.12679' from dual)
select to_utc_timestamp_tz(d default null on conversion error) converted
  from data;

ORA-00907: missing right parenthesis
00907. 00000 - "missing right parenthesis"
*Cause: 
*Action:
Error at Line: 11 Column: 30

Documentation Bug

I have filed an SR with Oracle Support regarding this error. It has been classified as documentation bug:

DOC 30936692 – TAKE OUT “[ DEFAULT RETURN_VALUE ON CONVERSION ERROR ]” OPTIONAL CLAUSE FROM TO_UTC_TIMESTAMP_TZ

and will be removed from future (>20) documentations.

Conclusion

Somehow I hopped, that Oracle would add the missing functionality concerning the conversion error handling to the TO_UTC_TIMESTAMP_TZ function rather than declaring it as a documentation bug. 

Not having a decent way to handle exceptions (besides building a user defined function with an exception handler) will keep me away from using this function.

Best way to loop over a dense nested table

24 Monday Jun 2019

Posted by in ORACLE, PL/SQL, Tipps and Tricks

≈ Leave a comment

Tags

,

“What is the best way to process a dense nested table?” seems to be a simple question with an quite simple answer…

Looping First to Last

One way to implement this kind of loop is making use of the collection methods first and last to write code like:

begin
   for i in myArray.first() .. myArray.last() loop
      ... do whatever you need to do
   end loop;
end;

This works as long as the array is initialized and not empty.

If the array is not initialized you get a

ORA-06531: Reference to uninitialized collection

exception. This because the first/last methods can only be used on an initialized collection. If the collection is initilized but empty, the exception thrown is:

ORA-06502: PL/SQL: numeric or value error

the reason for this error is, that on an empty array first and last return null as the result and this is not feasible for a numeric for loop.

So the correct implemetation using the first/last version of a loop would be:

begin
   if myArray is not null
      and myArray is not empty
   then
      for i in myArray.first() .. myArray.last() loop
         ... do whatever you need to do
      end loop;
   end if;
end;

but…we can do better (and shorter).

Looping 1 to count

A slightly different approach is to use 1 as the lower and count as the upper border. If count is called on an empty collection we get 0 as result which has the effect that the loop body is never executed. But as with last/first we get an error when count is used on a collection that has not been initialized. 

So the solution using 1..count would look like this:

begin
   if myArray is not null
   then
      for i 1 .. myArray.count() loop
         ... do whatever you need to do
      end loop;
   end if;
end;

but again…we can do better (and shorter).

Looping using cardinality

Instead of using count to get the number of entries of a nested table we can also use the cardinality function. The advantage of cardinality is that this function can be called on not initialized collections too. The result of cardinality in this case would be null. Null however is not a valid value for a loop border so the cardinality call has to be surrounded by a nvl/coalesce function.

begin
   for i 1 .. nvl(cardinality(myArray),0) loop
      ... do whatever you need to do
   end loop;
end;

This is the shortes way to loop over a collection and it is a version that handles not initialized and empty collections too.

ORACLE 18c – Qualified Expressions

21 Wednesday Feb 2018

Posted by in ORACLE, ORACLE 18c, PL/SQL

≈ Leave a comment

Tags

, ,

Qualified expressions improve readability and maintainability of our PL/SQL code. 

While initialising nested tables was quite easy in the past, initialising associative arrays was quite a pain, if it was not done using a query. 

The following examples show how qualified expressions simplify the coding using an associative array, where the key is the name of a city and the content is the population.

Before 18c

DECLARE
   TYPE city_population_type IS TABLE OF NUMBER INDEX BY VARCHAR2(100);
   city_population city_population_type;
BEGIN
   <<Init>>
   BEGIN
      city_population('Zürich')   := 400000;
      city_population('Genf')     := 195000;
      city_population('Basel')    := 175000;
      city_population('Bern')     := 140000;
      city_population('Lausanne') := 135000;
   END Init;
 
   dbms_output.put_line('Population Basel : ' || city_population('Basel'));
END;
/

18c using qualified expression

DECLARE   
   TYPE city_population_type IS TABLE OF NUMBER INDEX BY VARCHAR2(100);   
   city_population city_population_type;
BEGIN   
   <<Init>>   
   BEGIN       
      city_population := city_population_type('Zürich'   => 400000
                                             ,'Genf'     => 195000
                                             ,'Basel'    => 175000
                                             ,'Bern'     => 140000
                                             ,'Lausanne' => 135000);
   END Init;      
  
   dbms_output.put_line('Population Basel : ' || city_population('Basel'));
END;
/

Qualified expression can also be used when working with record structures (and of course also when record structures are part of a associative array).

Record structures with qualified expressions

DECLARE
   TYPE emp_type IS RECORD(empno    emp.empno%type
                          ,ename    emp.ename%type
                          ,job      emp.job%type
                          ,mgr      emp.mgr%type 
                          ,hiredate emp.hiredate%type
                          ,sal      emp.sal%type
                          ,comm     emp.comm%type 
                          ,deptno   emp.deptno%type);

   TYPE emp_tab_type IS TABLE OF emp_type INDEX BY PLS_INTEGER;
   emp_tab emp_tab_type;
BEGIN
   emp_tab := emp_tab_type(7369 => emp_type(ename => 'Smith'
                                          , Job => 'Clerk')
                          ,7782 => emp_type(ename => 'Clark'
                                          , Job => 'Manager'
                                          , Sal => 3000)
                          ,7902 => emp_type(ename => 'Ford'
                                          , Sal => 3000
                                          , Deptno => 20));
 
   dbms_output.put_line(emp_tab(7782).ename);
END;
/

Now…normally we would make use of %ROWTYPE when we need a record structure that reflects a table row…

DECLARE
   TYPE emp_tab_type IS TABLE OF emp%rowtype INDEX BY PLS_INTEGER;
   emp_tab emp_tab_type;
BEGIN
   emp_tab := emp_tab_type(7369 => ?(ename => 'Smith'
                                   , Job => 'Clerk')
                          ,7782 => ?(ename => 'Clark'
                                   , Job => 'Manager'
                                   , Sal => 3000)
                          ,7902 => ?(ename => 'Ford'
                                   , Sal => 3000
                                   , Deptno => 20)); 
   dbms_output.put_line(emp_tab(7782).ename);
END;
/

Hmm….what “constructor” should we use if the associative array holds a rowtype? But…perhaps a subtype could help.

DECLARE
   SUBTYPE emp_row_type IS emp%ROWTYPE;
   TYPE emp_tab_type IS TABLE OF emp_row_type INDEX BY PLS_INTEGER;
   emp_tab emp_tab_type;
BEGIN
   emp_tab := emp_tab_type(7369 => emp_row_type(ename => 'Smith'
                                              , Job => 'Clerk')
                          ,7782 => emp_row_type(ename => 'Clark'
                                              , Job => 'Manager'
                                              , Sal => 3000)
                          ,7902 => emp_row_type(ename => 'Ford'
                                              , Sal => 3000
                                              , Deptno => 20)); 
   dbms_output.put_line(emp_tab(7782).ename);
END;
/

ORA-06550: line 6, column 36: 
PLS-00222: no function with name 'EMP_ROW_TYPE' exists in this scope

Too bad…that would have been nice too.

Conclusion

Qualified expression will help us to write code that is more readable and easier. It’s a nice new feature that I will use for sure.

And perhaps, someday, the %ROWTYPE thing will be possible too.

 

 

 

NULLIF – A tiny, powerfull and underrated SQL Function

19 Monday Jun 2017

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

≈ Leave a comment

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.

Grouping and counting continuity

15 Tuesday Dec 2015

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

≈ Leave a comment

Tags

, , , , ,

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 in ORACLE, SQL, Tipps and Tricks

≈ Leave a comment

Tags

, ,

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 in ORACLE, SQL, Tipps and Tricks

≈ Leave a comment

Tags

, ,

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 in ORACLE, ORACLE 11g Release 2, SQL

≈ Leave a comment

Tags

, , ,

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.

ORACLE 12c Release 1 – Truncate Cascade

17 Tuesday Sep 2013

Posted by in ORACLE, ORACLE 12c Release 1, SQL

≈ Leave a comment

Tags

, ,

Prior to ORACLE 12c truncating a table referenced by enabled foreign keys was no possible (even if the tables were empty).

ORA-02266: unique/primary keys in table referenced by enabled foreign keys

ORACLE 12c adds a new option to the TRUNCATE command

  • Parent and all children tables will be truncated if the foreign key between (all of) them is defined as ON DELETE CASCADE
  • Even if no parent exists (optional foreign key)
  • Even if the foreign key is defined to be novalidate
  • It is a truncate not a delete

Example

REM ===========================================================================
REM Create tables
REM ===========================================================================
CREATE TABLE dept_trunc_casc_demo (
   deptno NUMBER
  ,dname  VARCHAR2(30)
  ,CONSTRAINT dept_trunc_cast_demo_pk PRIMARY KEY (deptno)
  )
/


CREATE TABLE emp_trunc_casc_demo (
   empno  NUMBER
  ,ename  VARCHAR2(30)
  ,deptno NUMBER
  ,CONSTRAINT emp_trunc_casc_demo_pk PRIMARY KEY (empno)
  ,CONSTRAINT emp_dept_trunc_casc_fk FOREIGN KEY (deptno) REFERENCES dept_trunc_casc_demo
  )
/


REM ===========================================================================
REM Add data
REM ===========================================================================
INSERT INTO dept_trunc_casc_demo (deptno, dname) VALUES (10,'ACCOUNTING');
INSERT INTO dept_trunc_casc_demo (deptno, dname) VALUES (20,'SALES');

INSERT INTO emp_trunc_casc_demo (empno, ename, deptno) VALUES (1,'KUNZ',10);
INSERT INTO emp_trunc_casc_demo (empno, ename, deptno) VALUES (2,'MEIER',20);
INSERT INTO emp_trunc_casc_demo (empno, ename, deptno) VALUES (3,'HUBER',20);

COMMIT;

REM ===========================================================================
REM Trying to truncate master table
REM Will not work as with prior versions of ORACLE
REM ===========================================================================
TRUNCATE TABLE dept_trunc_casc_demo;

TRUNCATE TABLE dept_trunc_casc_demo
               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

REM ===========================================================================
REM Trying to truncate master table with new keyword CASCADE
REM This will not work unless there is a FK with on delete cascade.
REM ===========================================================================
TRUNCATE TABLE dept_trunc_casc_demo CASCADE;

TRUNCATE TABLE dept_trunc_casc_demo CASCADE;
               *
ERROR at line 1:
ORA-14705: unique or primary keys referenced by enabled foreign keys in table "O12TEST"."EMP_TRUNC_CASC_DEMO"

REM ===========================================================================
REM Changing the foreign key to cascade delete
REM ===========================================================================
ALTER TABLE emp_trunc_casc_demo DROP CONSTRAINT emp_dept_trunc_casc_fk;

ALTER TABLE emp_trunc_casc_demo ADD CONSTRAINT emp_dept_trunc_casc_fk
   FOREIGN KEY (deptno) REFERENCES dept_trunc_casc_demo ON DELETE CASCADE;

REM ===========================================================================
REM Truncating Master Table with cascade keyword
REM ===========================================================================
TRUNCATE TABLE dept_trunc_casc_demo cascade;

REM ===========================================================================
REM Show number of rows in child table
REM ===========================================================================

SELECT COUNT(*)
  FROM emp_trunc_casc_demo;

COUNT(*)
--------
       0

Date arithmetic

03 Tuesday Sep 2013

Posted by in ORACLE, SQL, Tipps and Tricks

≈ Leave a comment

Tags

, ,

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.