SQL - PIVOT clause Series - Basics

PIVOT is a tricky clause in SQL. One way it is easy if it is completely understood. It is very difficult, if you don’t know how it works. This post starts with basics of PIVOT clause with examples.

Basically, PIVOT converts a row data into column data. Let’s understand what that means with an example.

Assume a table as shown below. Scripts to create table structure and sample data are given here.

Now, let’s assume you are CEO and you want to analyze the current budgets for each department and for each role that you are currently investing to run company operations.

A simple select will give a data as shown in this screenshot.

SELECT department, role, annual_salary

FROM employees

WHERE department IS NOT NULL

This data makes sense but this is not it, as a CEO you are interested more in comparing the data like, the budget for SVP's in Sales department and Computer Services, Total salesman’s budget etc…

In short, the following summary type of view makes more sense than just the raw data (Or data as it is). Following screenshot has sum of salaries listed for each department and for each role. Columns with empty fields means there is no role in that department.

Budget comparison is much easier this way, isn't it?

This can be easily done using PIVOT clause, here is the SQL Code.

SQL Code:

SELECT *
FROM
(     /* Base query */
      SELECT department, role, annual_salary
      FROM employees
      WHERE department IS NOT NULL
)
PIVOT
(
  SUM(annual_salary) AS total_budget
  FOR(department)
  IN ('Computer Services' AS computer_services,
      'Human Resource Management' AS hr,
      'Sales' AS sales
     )
)
ORDER BY role

Read more on PIVOT @ 

RetroPay Series - Oracle Payroll Event Model (PEM)

Oracle Payroll Event model (PEM) is the basic architecture which is used by Retropay, Continuous calculations, Proration, iterative engine etc…

This post describes about PEM architecture and its setups needed.

Basic function of PEM architecture is to capture and process the runtime changes (also named as events) that are caused by the manual changes recorded by users in HRMS, for example compensation changes, supervisor changes, location changes etc…

This is simple architecture; it has 3 steps, Event Capture, Incident Register and Event Interpretation.

Each phase has to be configured to restrict/process the actual events that need to be tracked. It works like an AUDIT system, but more tailored to what is actually interesting.

Step 1) Event Capture:

Configuring event capture is the first and important step in PEM. It is done by enabling dynamic triggers. Dynamic triggers are nothing but the database triggers which are generated automatically using the form. Navigation Super HRMS Manager Responsibility -> Other Definitions -> Dynamic Triggers Form

The following screenshot shows the options to query existing dynamic triggers on a specific table.

When clicked on Find button, Actual trigger definition is displayed. No need to add any code, it is auto generated. All that we need to make sure is to check if “Generated” and “Enabled” check boxes are selected. Dynamic triggers are also called as continuous calculation trigger, as the name says, this trigger enables the tracking on the desired changes continuously at runtime. Triggered code can be viewed using “view source” button.

General Naming Convention for the name of dynamic trigger is given below.

- Name of Insert Trigger on PAY_ELEMENT_ENTRIES_F is PAY_ELEMENT_ENTRIES_F_ARI

- Name of Update Trigger on PAY_ELEMENT_ENTRIES_F is PAY_ELEMENT_ENTRIES_F_ARU

- Name of Delete Trigger on PAY_ELEMENT_ENTRIES_F is PAY_ELEMENT_ENTRIES_F_ARD

Step 2) Incident Register:

Configuring Incident Register is nothing but the high level functional areas for which the events (changes) have to be tracked. It is obvious that events can be tracked only for the tables which have dynamic triggers enabled in previous step. Configuring Incident Register facilitates the previous step.

After configuring this step system immediately start recording the changes.

Navigation, Super HRMS Manager Responsibility -> Other Definitions -> Functional Areas Form and query for “INCIDENT REGISTER”; Event capture can be enabled at 3 levels.

•  Trigger Level: If events on specific table have to be tracked. Screenshot below is configured to track Inserts, updates and Deletes on PAY_ELEMENT_ENTRIES_F table.

• Legislation level: If all the events at legislation level have to be tracked. Screenshot below is configured to track all events (Inserts, updates and deletes) at US, MX and FR legislations.

• Business Group level: If all the events at business group level have to be tracked. Screenshot below is configured to track all events (Inserts, updates and deletes) at Progress UK and Vision Corporation business groups.

Step 3) Event Interpretation:

This step is nothing but configuring system to look for specific events out of all the events that are recorded in the system (because of HR changes). This is done by defining event group, which is used in Retropay and Proration. Navigation, Super HRMS Manager Responsibility -> Other Definitions -> Define Event Group

Following screenshot shows the event group definition, which is expected to interpret on Datetrack Inserts, Datetrack Delete, Datetrack Correction on a specific column, Datetrack Update on a specific column. This inclusion may vary depending on how you want retro process to pick your changes. This screenshot will only pick up changes to element entry values, because it has only those events included.

This event group can only be used in Retropay processing, because event group type is selected as Retro (Other choice is Proration).

Want to read more? Then read HRMS DEVELOPMENT WHITE PAPER - Continuous Calculation (Doc ID 188154.1) 

Outer Joins - ANSI vs Oracle Join (+) Syntax

There are many problems or rather restrictions to use oracle specific join (+) operator while writing SQLs. In fact, there are few places where (+) syntax can't be used.
Let's see them with examples.

Oracle itself recommends that you use the FROM clause OUTER JOIN (ANSI) syntax rather than the Oracle join operator.

Did you know? 
Oracle developed it's (+) join syntax before the ANSI committee had completed the definitions for the joins. That's why Oracle is still supporting (+) join operator for backwards compatibility reasons.

Oracle Specific Syntax:

    SELECT EMP.name, DEP.name 
    FROM employee EMP, department DEP 
    WHERE EMP.dep_id = DEP.dep_id (+);

ANSI Syntax:

    SELECT EMP.name, DEP.name     
    FROM employee EMP          
       LEFT OUTER JOIN department DEP 
        ON EMP.dep_id = DEP.dep_id;

1) ANSI standard join notation comes with the advantage that it is not vendor specific and will be same in any RDBMS (like Microsoft Sql Server, MySql etc.). So you don't need to touch the logic, if you are porting your database to non-oracle RDBMS. 
Or let me put this way. A non-oracle database developer can also understand your code, if it written using ANSI syntax. So, you have wider audience who can understand and maintain your code. ANSI syntax is more readable too.
   
2) You can't do FULL OUTER JOIN on two tables using oracle (+) syntax. 
    You have to do it manually with a UNION ALL of two joins.
   
 Example:
Assume 3 tables. A is related to B and A is also related to C.

Wrong Query:

         SELECT A.*, B.*
         FROM tableA A, tableB B
         WHERE A.col1 (+) = B.col1 (+);

        ORA-01468: a predicate may reference only one outer-joined table

Correct Query:

         SELECT A.*, B.* FROM tableA A, tableB B WHERE A.col1 (+) = B.col1 UNION ALL SELECT A.*, B.* FROM tableA A, tableB B WHERE A.col1 = B.col1 (+);

ANSI Syntax:

SELECT A.*, B.*
FROM tableA A 
             FULL OUTER JOIN tableB B
ON A.col1 = B.col1;

3) Using (+) syntax, you can't OUTER JOIN a table to two or more tables.
   You have to manually create a subquery to achieve this

       Example:
Assume 3 tables. A is related to B and B is related to C.

Wrong Query

  SELECT A.*, B.*, C.*
FROM tableA A, tableB B, tableC C
WHERE B.col1  = A.col1 (+)
       AND  C.col2 = A.col2 (+) 

ORA-01417: a table may be outer joined to at most one other table

Correct Query

SELECT C.*
FROM
       (
SELECT A.*, B.*
FROM tableA A, tableB B
WHERE B.col1  = A.col1 (+)
       ) TEMP, C
WHERE  C.col2 = TEMP.col2 (+) 

ANSI Syntax

SELECT A.*, B.*
FROM tableA A 
         RIGHT OUTER JOIN tableB B
           ON B.col1 = A.col1
         RIGHT OUTER JOIN tableC C
            ON C.col2 = A.col2

4) You cannot specify the (+) operator in a query block that also contains FROM clause join syntax.

   Wrong Usage
SELECT temp.*, C.*
FROM (tableA A  JOIN tableB B ON B.col1 = A.col1) temp
            , C   
         WHERE  C.col2 = temp.col2

5) The (+) operator can appear only in the WHERE clause or, 
   in the context of left-correlation (when specifying the TABLE clause) in the FROM clause, and can be applied only to a column of a table or view.  

6) If A and B are joined by multiple join conditions, 
   then you must use the (+) operator in all of these conditions. 
   If you do not, then Oracle Database will return only the rows resulting 
   from a simple join, but without a warning or error to advise you that 
   you do not have the results of an outer join.

          Wrong Usage (Though syntactically correct)
SELECT A.*, B.*
FROM tableA A, tableB B
WHERE B.col1  = A.col1 (+)
AND B.col2    = A.col2
AND B.col3    = A.col3

   Correct Usage 

SELECT A.*, B.*
FROM tableA A, tableB B
WHERE B.col1  = A.col1 (+)
AND B.col2    = A.col2 (+)
AND B.col3    = A.col3 (+)

ANSI Syntax

SELECT A.*, B.*
FROM tableA A 
RIGHT OUTER JOIN tableB B
ON B.col1  = A.col1
AND B.col2    = A.col2
AND B.col3    = A.col3

7) The (+) operator does not produce an outer join, if you specify one table in 
   the outer query and the other table in an inner query.

Wrong Query

SELECT A.*, B.*
FROM tableA A, tableB B
WHERE A.col1 = B.col1
AND A.col2 IN (SELECT C.col2 
                       FROM tableC 
                       WHERE C.col2 = A.col2(+) 
                       )
ORA-01705: an outer join cannot be specified on a correlation column

Right Query

SELECT A.*, B.*
FROM tableA A, tableB B
WHERE A.col1 = B.col1
AND A.col2 IN (SELECT C.col2 
                       FROM tableC 
                       WHERE C.col2 = A.col2
                       )

8) You cannot use the (+) operator to outer-join a table to itself, although self joins are valid.

Wrong Query

SELECT * 
FROM tableA
WHERE A.col1 = A.col2 (+)
ORA-01416: two tables cannot be outer-joined to each other

Right Queries

SELECT * 
FROM tableA
WHERE A.col1 = A.col2;
(Inner joins are valid on the same table)
--
SELECT * 
        FROM tableA A1, tableA A2
WHERE A1.col1 = A2.col2 (+)

ANSI Syntax (Mandates for self join)

SELECT * 
FROM tableA A1 
LEFT OUTER JOIN tableA A2
   ON A1.col1 = A2.col2

9) The (+) operator can be applied only to a column, not to an arbitrary expression. 
   However, an arbitrary expression can contain one or more columns marked with the (+) operator.  

Wrong Query

SELECT A.*, B.*
FROM tableA A, tableB B
WHERE A.col1 = (B.col1 * 123 )(+)
ORA-00936: missing expression
--
SELECT A.*, B.*
FROM tableA A, tableB B
WHERE A.col1 = B.col1 * B.col2(+)
ORA-01417: a table may be outer joined to at most one other table

Right Query

SELECT A.*, B.*
FROM tableA A, tableB B
WHERE A.col1 = B.col1 (+) * 123
--
SELECT A.*, B.*
FROM tableA A, tableB B
WHERE A.col1 = B.col1(+) * B.col2(+)

ANSI Syntax

SELECT A.*, B.*
FROM tableA A 
           LEFT OUTER JOIN tableB B
ON A.col1 = B.col1 * 123

10) A WHERE condition containing the (+) operator cannot be combined with another condition using the OR logical operator

Wrong Query

SELECT A.*, B.*
FROM tableA A, tableB B
WHERE (A.col1 = B.col1 (+) 
              OR
           A.col2 = B.col2)

ORA-01719: outer join operator (+) not allowed in operand of OR or IN

Right Query

SELECT A.*, B.*
FROM tableA A, tableB B
WHERE (A.col1 = B.col1 
            OR
          A.col2 = B.col2)

ANSI Syntax

SELECT A.*, B.*
FROM tableA A
RIGHT OUTER JOIN tableB B
ON ( A.col1 =  B.col1
OR 
                A.col2 = B.col2)

11) A WHERE condition cannot use the IN comparison condition to compare a column marked with the (+) operator with an expression.
Similar to the above point.

Wrong Query

SELECT A.*, B.*
FROM tableA A, tableB B
WHERE A.col1 = B.col1 (+) 
       AND A.col1(+) IN (1,2,3)

ORA-01719: outer join operator (+) not allowed in operand of OR or IN

Right Query (But this is no more an outer join)

SELECT A.*, B.*
FROM tableA A, tableB B
WHERE A.col1 = B.col1 (+) 
       AND A.col1 IN (1,2,3)

ANSI Syntax

SELECT A.*, B.*
FROM tableA A
RIGHT OUTER JOIN tableB B
ON A.col1 =  B.col1
AND A.col1 IN (1,2,3)

Reference
Oracle® Database SQL Language Reference (11g Release 1)

Oracle LAG Function

LAG is an analytic function, which was introduced in Oracle 8.1.6. It gives access to multiple rows within a table, without the need for a self-join.

Syntax:
LAG ( expression [, offset [, default] ] )
   OVER ( [ query_partition_clause ] order_by_clause )

This post describes an example usage of this LAG function. PRODUCTS table with the sample data as shown in the screenshot below is assumed for this example.

Scripts to create table structure and sample data are given here.

Idea is to organize the products into categories and brands as shown in the screenshot below and show category/brand name only once for per category/brand.

This output can be achieved using LAG function, without any efforts.

SQL Code:

SELECT 
  --

  DECODE( LAG(DATA.product_category) 

            OVER (ORDER BY DATA.product_category, DATA.brand, DATA.product_name), 

        DATA.product_category, 

        NULL, 

        DATA.product_category) product_category,
  --

  DECODE( LAG(DATA.brand) 

            OVER (ORDER BY DATA.product_category, DATA.brand, DATA.product_name), 

         DATA.brand, 

         NULL, 

         DATA.brand) brand,
  --

  DATA.product_name,

  DATA.price_per_unit,

  DATA.currency,

  DATA.product_id

FROM products DATA

Read more about LAGFunction here…

Classic SQL Technique to generate row numbers

Idea of this post, is to preserve the classic old technique of generating Row Numbers using SQL.
Simple answer for this can be to use ROW_NUMBER()..OVER..PARTITION BY clause in Oracle.

People used to generate row numbers, even before this clause was instroduced.
Here is an example, which explains this technique.

Consider ORDERS table which has the data as shown below.
One Order can contain multiple products. (Sample Script here)

Now, assume the sequence number has to be generated for each order as shown below.

Using ROW_NUMBER() Function:
SELECT o.*, ROW_NUMBER ()
       OVER (PARTITION BY order_id ORDER BY product_id) AS SeqNo
   
  FROM orders o;

Without using ROW_NUMBER() Function:
SELECT   t4.seqno, t1.*
    FROM orders t1
         JOIN
         (SELECT   COUNT (*) seqno, t2.product_id, t2.order_id
              FROM orders t2 INNER JOIN orders t3
                   ON t2.product_id >= t3.product_id
                 AND t2.order_id = t3.order_id
          GROUP BY t2.product_id, t2.order_id) t4
         ON t4.order_id = t1.order_id AND t4.product_id = t1.product_id
ORDER BY t1.order_id, t1.product_id;


Pros:

• Suitable if you are working with small databases like SQLLite. Note that, this is SQL technique which involves only joins and can be used with any database.

Cons:

• Slow, if we are talking tables with large amount of data.
• Won't work if the sequence number has to be generated w.r.to VARCHAR column. It only work with NUMBER column.

Like this topic? Jump on to comments section and leave your feedback.

Cheers!!