• Home
  • SQL & PL/SQL Guidelines
  • Publications / Talks
  • Videos
  • Monday Morning Tech Talk
  • About
  • Contact

Just another ORACLE Development blog

Just another ORACLE Development blog

Category Archives: Tipps and Tricks

Partitioned Outer Join

16 Tuesday Jun 2015

Posted by troroblog in 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;

Date arithmetic

03 Tuesday Sep 2013

Posted by troroblog in 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.

Column to rows

23 Thursday May 2013

Posted by troroblog in Tipps and Tricks

≈ Leave a comment

Tags

CONNECT BY, ORACLE, Regular Expression, SQL

How can we convert a string including seperators into it’s token using only SQL?

Easiest way is to make use of recursive query techniques and regular expression as shown below:

WITH data (string) AS (SELECT 'MEIER,HUBER,MUELLER,KUHN,,KOLLER' FROM dual)
SELECT REGEXP_SUBSTR (data.string, '[^,]+', 1, ROWNUM) element_string  
  FROM data 
CONNECT BY LEVEL <= LENGTH (REGEXP_REPLACE (DATA.STRING, '[^,]+'))  + 1
/

ELEMENT_STRING
--------------
MEIER
HUBER
MUELLER
KUHN
KOLLER
(null)

Working with a single date validity column

08 Monday Aug 2011

Posted by troroblog in Tipps and Tricks

≈ Leave a comment

Tags

DATE, ORACLE, SQL, VALIDITY

Having a single column to determine the validity of a value has many advantages over a two column (valid_from / valid_to) approach. 

  • no need to care about gaps 
  • no need to care about overlaps
  • simple way to prevent duplicates possible (unique constraint)

Therefore this is often seen in parameter tables or any other tables storing different versions of an information with a time range validity.

The problem occurs when you try to use the correct information in your queries.


Prepare test case:

Our test case has an application parameter table (key,value,valid_from) holding information about the tax rate of the swiss vat.

CREATE TABLE appl_params (
    param_name VARCHAR2(20)  NOT NULL
   ,param_value       VARCHAR2(200) NOT NULL
   ,param_valid_from  DATE          NOT NULL)
/

A simple primary key on param_name, param_valid_from is sufficient to avoid duplicate validities for one parameter at a point in time.

ALTER TABLE appl_params 
   ADD CONSTRAINT appl_params_pk 
   PRIMARY KEY (param_name, param_valid_from)
/
INSERT INTO appl_params VALUES('SWISS VAT','7.6',TO_DATE('01.01.2005','dd.mm.yyyy'));
INSERT INTO appl_params VALUES('SWISS VAT','8.0',TO_DATE('01.01.2011','dd.mm.yyyy'));
INSERT INTO appl_params VALUES('SWISS VAT','7.0',TO_DATE('01.01.2014','dd.mm.yyyy'));
COMMIT;

To get the correct tax rate for a sale we need to find the rate belonging to the valid_from which is the latest before the sale takes place…. This is a rather complicated query.

WITH sales (prod, sale_date, price)
        AS (SELECT 'Product A', date '2009-07-27', 115 FROM dual UNION ALL
            SELECT 'Product A', date '2010-07-27', 115 FROM dual UNION ALL
            SELECT 'Product A', date '2011-07-27', 115 FROM dual UNION ALL
            SELECT 'Product A', date '2013-07-27', 115 FROM dual UNION ALL
            SELECT 'Product A', date '2015-07-27', 115 FROM dual)
 SELECT prod
      , sale_date
      , price
      , TO_NUMBER(ap.param_value)
      , price + (price * TO_NUMBER(ap.param_value)/100) AS price_incl_vat
   FROM sales s
       ,appl_params ap
  WHERE ap.param_name = 'SWISS VAT'
    AND ap.param_valid_from = (SELECT MAX(ap2.param_valid_from)
                                 FROM appl_params ap2
                                WHERE ap2.param_name = ap.param_name
                                  AND ap2.param_valid_from <= s.sale_date) 
/

To support easier and more intuitive queries we create a view which has an additional column param_valid_to. To populate this column we use the analytic function lead.

The solution underneath assumes that param_valid_to as well as sale_date do not include relevant time information (all date columns are truncated to midnight).

CREATE VIEW appl_params_vw
AS
SELECT param_name, param_value, param_valid_from
     , LEAD(param_valid_from,1,date '3000-01-01') 
             OVER (PARTITION BY param_name
  ORDER BY param_valid_from ASC) - 1 AS param_valid_to
  FROM appl_params             
/

After having created this view, the query to find the appropriate tax rate gets quite easy.

WITH sales (prod, sale_date, price)
        AS (SELECT 'Product A', date '2009-07-27', 115 FROM dual UNION ALL
            SELECT 'Product A', date '2010-07-27', 115 FROM dual UNION ALL
            SELECT 'Product A', date '2011-07-27', 115 FROM dual UNION ALL
            SELECT 'Product A', date '2013-07-27', 115 FROM dual UNION ALL
            SELECT 'Product A', date '2015-07-27', 115 FROM dual)
SELECT s.prod
     , s.sale_date
     , s.price
     , TO_NUMBER(v.param_value) vat
     , s.price + (s.price * to_number(v.param_value)/100) AS price_incl_vat
  FROM            sales          s 
       INNER JOIN appl_params_vw v ON (    v.param_name = 'SWISS VAT'
                                       AND s.sale_date BETWEEN v.param_valid_from 
                                                           AND v.param_valid_to)
/

If the approach is not fast enough we would also have the possibility to materialize this view which is, for a table like the parameter table where changes do not happen to often, not a bad idea.

Calculating the number of weekdays between two dates

14 Thursday Jul 2011

Posted by troroblog in Tipps and Tricks

≈ Leave a comment

Tags

DATE, ORACLE, SQL

How can we calculate the number of business days (Monday – Friday) between two given dates….

This is a question often asked in the SQL community.

Two solutions for this problem:


Solution 1:

Calculate the number of days between two date values and subtract the number of Saturdays and Sundays in between.

WITH DATA AS (SELECT TO_DATE('05.09.2011','dd.mm.yyyy') date_from                    ,TO_DATE('31.10.2011','dd.mm.yyyy') date_to                FROM dual)
SELECT date_to - date_from + 1
     - (((NEXT_DAY(date_to-7,'Saturday') - NEXT_DAY(date_from,'Saturday'))/7) + 1)
     - (((NEXT_DAY(date_to-7,'Sunday')   - NEXT_DAY(date_from,'Sunday'))/7) + 1)
  FROM data
/

Solution 2:

Count the number of days that are neither Saturdays nor Sundays between two date values using a recursive query.

SELECT COUNT(DECODE(TO_CHAR(TO_DATE('05.09.2011','dd.mm.yyyy') + (ROWNUM - 1),'DY'),'SAT',NULL,'SUN',NULL,'1'))
   FROM dual
 CONNECT BY ROWNUM <= (TO_DATE('31.10.2011','dd.mm.yyyy') - TO_DATE('05.09.2011','dd.mm.yyyy') + 1) 
/ 

Rounding monetary values

26 Friday Feb 2010

Posted by troroblog in Tipps and Tricks

≈ Leave a comment

Tags

ORACLE, ROUNDING, SQL

A question that occurs from time to time is how to round a (monetary) amount to e.g. 5, 25, 50 cents. Basically this can be done easily using the ROUND function.

ROUNDING to 5 cents = ROUND(amount*20)/20
amount   multiplied by 20   rounded to integer   divided by 20
------   ----------------   ------------------   -------------
  1.02              20.40                   20            1.00 
  1.03              20.60                   21            1.05 
  1.37              27.40                   27            1.35

the same principle works for:

  •  round to 25 cents = ROUND(amount*4)/4
  •  round to 50 cents = ROUND(amount*2)/2

basically the general formula is:

ROUND(amount*(100/rounding_base)) / (100/rounding_base)

Finding Gaps

15 Saturday Aug 2009

Posted by troroblog in Tipps and Tricks, Uncategorized

≈ Leave a comment

Tags

ORACLE, SQL

One of the things often asked for is how to find gaps in a list of numbers. So here’s a rather old suggestion:

WITH gaps (id) AS (SELECT  1 FROM dual UNION ALL
                   SELECT  3 FROM dual UNION ALL
                   SELECT  4 FROM dual UNION ALL
                   SELECT  7 FROM dual UNION ALL
                   SELECT 15 FROM dual)
    ,borders AS (SELECT MIN(id) mins
                       ,MAX(id) maxs
                   FROM gaps)
SELECT mins + (ROWNUM - 1) AS missing_value
  FROM borders
CONNECT BY mins + (ROWNUM - 1) <> maxs
MINUS
SELECT id
  FROM gaps
/
Newer posts →

Disclaimer

The opinions I express on my blog are my own and not necessarily those of the company I am working for.

Archives

Tags

Analytic Functions CAST Compiler Error Compiler Warning CONNECT BY Data Conversion DATE DEFAULT FIRST_VALUE Identity Column LAST_VALUE LISTAGG Model Clause New Feature ORACLE ORACLE 11g Release 2 ORACLE 12c Release 1 ORACLE 12c Release 2 OUTER JOIN PL/SQL PRAGMA RECURSION Regular Expression ROUNDING Row Pattern Matching Sequence SQL TABIBITOSAN Top-N TO_CHAR TO_DATE TO_NUMBER Truncate Table VALIDATE_CONVERSION VALIDITY WM_CONCAT XMLAGG

Recent Posts

  • Antipatterns SQL & PL/SQL – Redefine oracle defined exceptions
  • ORA12R2 – IMPdp may change segment_column_id
  • Antipatterns SQL & PL/SQL – “SELECT *” even if you only need a few columns
  • Antipatterns SQL & PL/SQL – Substitute NULL with empty string
  • Antipatterns SQL & PL/SQL – Reusing table aliases in the same SQL statement

Blog at WordPress.com.

 

Loading Comments...