Archiv für Oracle SQL

How to create a database trigger

Hi,

In today’s article I will explain how you can create a trigger and what you can use it for. A trigger is a procedure within the database which is automatically executed by the database when an event takes place. I will focus on table triggers and there you have three events: INSERT, UPDATE, DELETE. That means when a row is inserted an event takes places and if you have a trigger defined for this event it is executed. What do you need this for? You can use this for several things. For example you want to fill some columns based on other columns. Or you want to log something in another table.

In my following example I will create two tables. One with the data itself and some kind of logging table. If I insert a new row to the table I will also add a row to the logging table with a timestamp and the action performed.

First we need the two tables:

CREATE TABLE TBL_TRIGGER_TEST(
    ROW_ID NUMBER,
    ROW_VALUE NUMBER,
    ROW_COMMENT VARCHAR2(1000)
);
 
CREATE TABLE TBL_TRIGGER_TEST_LOG(
    ROW_ID NUMBER,
    ROW_VALUE NUMBER,
    ROW_COMMENT VARCHAR2(1000),
    ROW_TS DATE,
    ROW_ACTION VARCHAR2(1)
);

The table TBL_TRIGGER_TEST is the main table. We have a ROW_ID, a ROW_VALUE and a ROW_COMMENT, just to have some columns. In the table TBL_TRIGGER_TEST_LOG we will log all actions on the main table. In the case of the insertion of a new record we will insert a copy of it with date & time to the logging table. In the ROW_ACTION column we will insert an I (= INSERT).

Now we need the trigger:

CREATE OR REPLACE TRIGGER TRG_TEST_INS BEFORE INSERT
ON TBL_TRIGGER_TEST FOR EACH ROW
BEGIN
  INSERT INTO TBL_TRIGGER_TEST_LOG
  VALUES(:new.ROW_ID, :new.ROW_VALUE, :new.ROW_COMMENT, sysdate, ‚I‘);
END;

A trigger is created via the CREATE OR REPLACE TRIGGER statement. If a trigger with the same name already exists it is overwritten. The next important thing is BEFORE. That defines when the trigger should be executed before the DML operation takes place or after. In the case of after you just write AFTER instead of BEFORE. INSERT is the kind of DML event. You can choose between INSERT, DELETE and UPDATE. When you select UPDATE you can also specify a column. Then you defined with ON followed by the table name on what table this trigger should be implemented. The FOR EACH ROW option defines that the trigger should be executed for each row that is inserted and not only once for all rows within the transaction.

After BEGIN the code itself starts. We are just inserting a new into the logging table. The first three columns will be filled with the values that are to be inserted in the main table. You can access them via :new. This is something like a pseudo row. Beside :new you can use :old to access the old values. That is important when you delete a row or update it.

You are not allowed to put a COMMIT in a trigger. The trigger is commited by the transaction that inserted a row in the main table. That’s why it is not needed here.

Now you can test the trigger. Just insert a new row to the main table and commit it. Afterwards you can show all rows from the logging table. You should find an exact copy of the row you’ve inserted to the main table and the timestamp should match the date/time when you’ve inserted it.

As a task you can try to implement a trigger for the delete event. When a row is deleted you want a copy in the logging table. The action flag should contain a ‘D’. The examples and the result to this task can be found in the download area. Have fun!

If you are interested in this or other advanced Cognos/SQL topics you can also attend training on my corporate homepage. At the moment my open courses are only available in German. Inhouse training and online courses are also available in English language. So check out my open trainings (German) or my online courses (English)!

Downloads

BLOG_0029_Create_Trigger_examples_&_task.zip

Oracle Analytic Functions III: RANK() & DENSE_RANK()

Today I’ve prepared a lesson in Oracle analytic functions. Basicly these functions are an enhancement of the aggregation functions like SUM, MAX, MIN, etc. With the help of this enhancement it’s possible to calculate different aggregations within the same query. With a classic group by you can only have one aggregation per query. With analytic functions you can have more than this. It’s also possible to calculate windows, i.e. to access rows before or after the current row which gives you the ability to create dynamic window sums, etc. This basic functionality is described in part 1 and part 2 of this tutorial. Beside these basics functions there are a lot of additional functions in the database.

Today I want to demonstrate the use of the functions RANK() and DENSE_RANK(). These are used to calculate rankings in lists. The ranking depends on the sorting order which you can define within each calculation, i.e. you can create one ranking based on turnover descending and another on turnover ascending within the same query. As with all analytical functions you can also use partitioning with the ranking functions. This gives you the ability to start a new ranking for each partition. We will see that in the example. Basicly RANK() and DENSE_RANK() work in the same way but the difference lays in the way how they deal with rows that have the same ranking. If you have two rows with the same turnover they receive the same rank. The function RANK() then skips the next rank and continues with the following one. In comparision with that DENSE_RANK() continues with the next rank. Here comes the first example to demonstrate the basic stuff. This example is based on the TBL_TEST table (it consists of four columns: DAY, PRODUCT_NO, PRODUCT_NAME, TURNOVER. See also the example files):

SELECT x.*,
       RANK() OVER(ORDER BY PRODUCT_NO) AS RANK_NO,
       DENSE_RANK() OVER(ORDER BY PRODUCT_NO) AS DRANK_NO
FROM (
      SELECT to_char(DAY,’YYYY-MM‘) AS MONTH,
             PRODUCT_NO,
             SUM(TURNOVER) as TURNOVER
      FROM TBL_TEST
      GROUP BY to_char(DAY,’YYYY-MM‘), PRODUCT_NO
) x

Result:

BLOG_0028_PIC01_Example_RANK_DENSE_RANK

This example works with a sub-query to aggregate the data to a month based level. Then in the main query RANK() and DENSE_RANK() are used to calculate a ranking based on the product-no column. For that PRODUCT_NO appears in the ORDER BY of the RANK and DENSE_RANK functions. In the results you can see that the first four rows have the same rank. The function RANK() continues with rank 5 then but DENSE_RANK with 2. That’s the difference between these two functions when rows have the same rank.

I’ve mentioned that it’s possible to create a new ranking which each partition. The next example deals with that:

SELECT x.*,
       RANK() OVER(PARTITION BY MONTH ORDER BY PRODUCT_NO) AS RANK_NO
FROM (
      SELECT to_char(DAY,’YYYY-MM‘) AS MONTH,
                    PRODUCT_NO,
                    SUM(TURNOVER) as TURNOVER
      FROM TBL_TEST
      GROUP BY to_char(DAY,’YYYY-MM‘), PRODUCT_NO
) x;

Result:

BLOG_0028_PIC02_Example_RANK_PARTITION_BY

Again we have a sub-query to aggregate daily values in the table to monthly values. In the main query we just added a PARTITION BY MONTH to the definition of the RANK_NO column and that changes the list in that way that we have a ranking per month now.

To finish todays article I’ve created a small task. The solution can be found in the download area:

Produce a list with products, days and turnover. Create a ranking based on the turnover (of product and day). The data should be filtered to January 2017. And in the result the 3 top days should be displayed. The list should look like this one:

BLOG_0028_PIC03_Task1_Result

That’s it for RANK and DENSE_RANK. I hope you enjoyed today’s lesson.

If you are interested in this or other advanced Cognos/SQL topics you can also attend training on my corporate homepage. At the moment my open courses are only available in German. Inhouse training and online courses are also available in English language. So check out my open trainings (German) or my online courses (English)!

Download

BLOG_0028_RANK_DENSE_RANK_Examples_TASK.zip

Group By extensions IV: Functions

Hi,

This is the last part of my article series on SQL’s Group By extensions. In the first part we dealt with the ROLLUP statement, the second part then covered the CUBE statement and in the third part we discovered flexibility with the GROUPING SETS. Today we will take a look at two functions that are sometimes used to filter some of the auto-generated sums of ROLLUP and CUBE. The names of the functions are:

  • GROUPING(<expression>)
  • GROUPING_ID(<expression1>, <expression2>, …, <expression n>)

The examples are based on the test data of part 2, i.e. the table looks something like this:

Day Product No Product Name Turnover
01.01.2017 1 Product 1 20
2 Product 2 30
3 Product 3 40
02.01.2017 1 Product 1 30
2 Product 2 40
3 Product 3 50
….
30.04.2017 3 Product 3 330

 

The GROUPING() function is used to check whether an expression was used in the grouping or not. Here comes a small example for that:

SELECT  to_char(DAY, ‚YYYY-Q‘) AS QUARTER_ID,
              to_char(DAY, ‚YYYY-MM‘) AS MONTH_ID,
              SUM(TURNOVER) AS TURNOVER,
              GROUPING(to_char(DAY, ‚YYYY-Q‘)) AS GRP_QUARTER,
              GROUPING(to_char(DAY, ‚YYYY-MM‘)) AS GRP_MONTH
     
FROM TBL_TEST
GROUP BY ROLLUP(to_char(DAY, ‚YYYY-Q‘), to_char(DAY, ‚YYYY-MM‘))
ORDER BY QUARTER_ID, MONTH_ID;
 

Result:

BLOG_0023_PIC01_Result_Query_1

In this example you can see how the GROUPING() function basically works. For the most detailed level (I.e. month) both functions return 0 as the month_id and the quarter_id columns are grouped by. For the quarter sums the month_id is not grouped by and therefore the GROUPING() function returns 1 for this column. For the total sum both id-columns are not in the GROUP BY and that leads to a result of 1 for both GROUPIN() functions.

You can use these results to decide what sums you want to keep or remove. For that you usually need these functions when you use CUBE or ROLLUP. With GROUPING SETS you normally don’t have the need for these functions as you can exactly define what sums you need.

The GROUPING_ID() function is the combination of several GROUPING() functions in the way that if you take several GROUPING results (which are always 1 or 0 = binary) and put them in a row you receive a binary string which can be interpreted as a decimal number and that one is returned by GROUPING_ID. You receive a different ID for each different kind of sum. You then only have one column with the ID instead of several columns as with the GROUPING() function. Now here is an example for that:

SELECT  to_char(DAY, ‚YYYY-Q‘) AS QUARTER_ID,
              to_char(DAY, ‚YYYY-MM‘) AS MONTH_ID,
              SUM(TURNOVER) AS TURNOVER,
              GROUPING(to_char(DAY, ‚YYYY-Q‘)) AS GRP_QUARTER,
              GROUPING(to_char(DAY, ‚YYYY-MM‘)) AS GRP_MONTH,
              GROUPING_ID(to_char(DAY, ‚YYYY-Q‘), to_char(DAY, ‚YYYY-MM‘)) AS GRP_ID
 
FROM TBL_TEST
GROUP BY ROLLUP(to_char(DAY, ‚YYYY-Q‘), to_char(DAY, ‚YYYY-MM‘))
ORDER BY QUARTER_ID, MONTH_ID;

Result:

BLOG_0023_PIC02_Result_Query_2

 

I’ve take the first example and added only the last row in the SELECT. This is the GROUPING_ID() function which takes several expressions. In this example we put the expressions of the QUARTER_ID and MONTH_ID columns. The order of the expressions is important. It defines on what position of the binary string the result of the GROUPING of the column is put. If you take a look at row number 4. The GROUPING of Quarter is 0 and the GROUPING of Month is 1. As an binary string this would be 01 and if you transform it to a decimal number it’s: 0*2+1*2° = 0 + 1 = 1. If we check line no. 7 we have 11 as a binary string and decimal it’s: 1*2 + 1*2° = 2 + 1 = 3.

Now another example for the GROUPING():

SELECT  to_char(DAY, ‚YYYY-Q‘) AS QUARTER_ID,
              to_char(DAY, ‚YYYY-MM‘) AS MONTH_ID,
              DAY,
              SUM(TURNOVER) AS TURNOVER,
              GROUPING(to_char(DAY, ‚YYYY-Q‘)) AS GRP_QUARTER,
              GROUPING(to_char(DAY, ‚YYYY-MM‘)) AS GRP_MONTH,
              GROUPING(DAY) AS GRP_DAY,
              GROUPING_ID(to_char(DAY, ‚YYYY-Q‘), to_char(DAY, ‚YYYY-MM‘),
                                      DAY) AS GRP_ID     
 
FROM TBL_TEST
WHERE to_char(DAY,’DD‘) IN (’01‘, ’02‘, ’03‘)
GROUP BY ROLLUP(to_char(DAY, ‚YYYY-Q‘), to_char(DAY, ‚YYYY-MM‘), DAY)
ORDER BY QUARTER_ID, MONTH_ID, DAY;

Result:

BLOG_0023_PIC03_Result_Query_3

This example is based on the second example. In addition I’ve added the DAY column in the SELECT and ROLLUP. Also I’ve added a GROUPING() for DAY and added it to the GROUPING_ID() function. A filter in the WHERE clause just limit the rows in the result. It’s pretty much the same as in the second example. But you have one column more in this example. The daily sums have a GROUPING_ID of 0, the monthly SUMS of 1, the quarterly sums of 3 and the total sum a GROUPING_ID of 7. The 7 is calculated like this: 111 = 1*2² + 1*2 + 1*2° = 4 + 2 + 1 = 7.

I hope it became clear a little how the functions GROUPING() and GROUPING_ID() works and what they can be used for. These functions can be used in the HAVING clause to filter out some sums. Also these functions can be used with CUBE and also GROUPING SETS.

The examples used in this article can be found at the very end of this article as zip-file. I will continue this series soon and we will then deal with some useful functions you can use if you are working with the rollup or cube statement.

If you are interested in this or other advanced Cognos/SQL topics you can also attend training on my corporate homepage. At the moment my open courses are only available in German. Inhouse training and online courses are also available in English language. So check out my open trainings (German) or my online courses (English)!

 

Downloads

BLOG_0023_Testdata_&_Examples.zip

Group By extensions III: GROUPING SETS

Hi,

again something on the GROUP BY extensions. Today we will deal with GROUPING SETS. Probably the most flexible and easiest way to create multiple GROUP BYs within one query. In part 1 of this series we discovered the ROLLUP clause, which let’s us automatically calculate totals along some kind of dimension, which is defined by the grouping columns. Part 2 described the CUBE clause which just simply calculate all possible sums based on the grouping columns.

GROUPING SETS let you exactly specify the totals you want to see in your query. If you just want a report total and some monthly totals then you can just define that and you get it. If you need all the totals for all levels of a dimension a ROLLUP might be easier, but in most cases GROUPING SETS will probably be.

Now let’s get to coding. The examples are based on the test data of part 2, i.e. the table looks something like this:

Day Product No Product Name Turnover
01.01.2017 1 Product 1 20
2 Product 2 30
3 Product 3 40
02.01.2017 1 Product 1 30
2 Product 2 40
3 Product 3 50
….
30.04.2017 3 Product 3 330

 

In my first example I just want a monthly toal and a report total. With GROUPING SETS it looks like this:

SELECT to_char(DAY,’MM/YYYY‘) AS MONTH,
            SUM(TURNOVER) AS TURNOVER
FROM TBL_TEST
GROUP BY GROUPING SETS( (to_char(DAY,’MM/YYYY‘)),
                                                    ()
                                           );

Result:

BLOG_0022_PIC01_Result_Query1

As you can see in this example you just put all the different GROUP BYs in different sets of brackets. All the columns that belong to a specific GROUP BY are put to one pair of brackets. The different GROUP BYs are then separated by comma. If a GROUP BY only consist of one column you could theoretically leave out the brackets. If you want a report total you leave the brackets empty.

Now another example with a monthly total, a report total and a total per month and product:
SELECT to_char(DAY,’MM/YYYY‘) AS MONTH,
            PRODUCT_NO,
            PRODUCT_NAME,
            SUM(TURNOVER) AS TURNOVER
FROM TBL_TEST
GROUP BY GROUPING SETS( to_char(DAY,’MM/YYYY‘),
                                            (),
                                           (to_char(DAY,’MM/YYYY‘), PRODUCT_NO, PRODUCT_NAME)
                                          )
ORDER BY MONTH, PRODUCT_NO NULLS LAST

Result:

BLOG_0022_PIC02_Result_Query2

In this example we have calculated three sums as you can see directly in the GROUPING SETS section of the SQL. This example could have been produced also with a ROLLUP if you change the clauses and leave out the first two rows of the GROUPING SETS section. As you can see often there are several ways to reach the target. For larger reports and if you need a lot of sums maybe ROLLUP makes it easier for you and the database. But sometimes you only need a few specific totals out of a lot of different combinations then maybe GROUPING SETS are easier to use.

The examples used in this article can be found at the very end of this article as zip-file. I will continue this series soon and we will then deal with some useful functions you can use if you are working with the GROUP BY extensions.

If you are interested in this or other advanced Cognos/SQL topics you can also attend training on my corporate homepage. At the moment my open courses are only available in German. Inhouse training and online courses are also available in English language. So check out my open trainings (German) or my online courses (English)!

 

Downloads

BLOG_0022_Testdata_Examples.zip

Group By extensions II: CUBE

This is the second part of the series about GROUP BY extensions. In the first part of this series we discovered the ROLLUP statement which lets you easily calculate totals on different levels on granularity without using several queries. ROLLUP just calculates the totals along an artificial dimension which can be defined through expressions for each level.

Beside ROLLUP there is another extension which is called CUBE. CUBE calculates all possible totals for the given expressions. If you have Month and Product in your query and using CUBE then there calculated for totals: Month, Product, Month and Product, Total Query.

Let’s think of the following table with test data:

Day
Product No
Product Name Turnover
01.01.2017 1 Product 1 20
2 Product 2 30
3 Product 3 40
02.01.2017 1 Product 1 30
2 Product 2 40
3 Product 3 50
….
30.04.2017 3 Product 3 330

 

The first example creates a cube for day and product (no + name):

SELECT to_char(DAY,’MM/YYYY‘) AS MONTH,
              PRODUCT_NO,
              PRODUCT_NAME,
              SUM(TURNOVER)
FROM TBL_TEST
GROUP BY CUBE ( to_char(DAY,’MM/YYYY‘),
                                (PRODUCT_NO, PRODUCT_NAME)
                              );

Result:

BLOG_0020_PIC01_Result_Query1

This query generates 20 rows. The first 12 rows are the most detailed totals, i.e. a total for month and product. Line 13 – 15 are the totals per product, line 16 – 19 are the totals per month and line 20 is the query total.

To get this result you only have to use the CUBE statement instead of ROLLUP. CUBE then generates all possible sums. If you want two columns to be treated as one (e.g. product no and product name) then you use extra brackets for it ( take a look at the example). Of course you could add more expressions to the CUBE but this always leads to a lot more sums. If you have three expressions in the CUBE then you’ll get 2³=8 totals.

That leads sometimes to the question what to do if you need only specific totals but not all. You could use a CUBE then and use the HAVING to filter out the not needed totals based on values in the grouping columns.

The second example shows how to do that:

SELECT to_char(DAY,’MM/YYYY‘) AS MONTH,
             PRODUCT_NO,
             PRODUCT_NAME,
             SUM(TURNOVER)
FROM TBL_TEST
GROUP BY CUBE ( to_char(DAY,’MM/YYYY‘),

                                (PRODUCT_NO, PRODUCT_NAME)

                               )
HAVING PRODUCT_NO IS NOT NULL AND to_char(DAY, ‚MM/YYYY‘) IS NULL
                OR PRODUCT_NO IS NULL AND to_char(DAY, ‚MM/YYYY‘) IS NOT NULL;

Result:

BLOG_0020_PIC02_Result_Query2

In this SQL I have added the two lines for the HAVING clause. With these two lines I tell the database what sums out of all possible sums I am really interested in. In order to do that I just check that either the month is null and product not or month is not null but product is. Only in these two cases I want the line to be displayed.

Like the ROLLUP CUBE is available on Oracle, MS SQL Server, DB2, Postgres, etc., but not on MySQL. I hope you enjoyed today’s lesson again. The examples used in this article can be found at the very end of this article as zip-file. I will continue this series soon and we will then deal with the so called GROUPING SETS which let you define sums and totals in a very detailed way.

If you are interested in this or other advanced Cognos/SQL topics you can also attend training on my corporate homepage. At the moment my open courses are only available in German. Inhouse training and online courses are also available in English language. So check out my open trainings (German) or my online courses (English)!

Download

BLOG_0020_Testdata_Examples_zip