| August 17, 2017
Keine Kommentare

New book: SQL Basics – Excerpt

Hi,

today I just want to give you an excerpt of my latest book SQL Basics – Learn SQL the easy way. The whole book is an introduction to relational databases and SQL. It starts with some theory like what is a database, relations between tables, normalisation, transactions, etc. After that the SQL queries are introduced starting with easier stuff like selecting columns, doing calculations, defining filters and sorting. After that multiple tables are queried with Joins (Inner, Outer, Cross) and data is aggregated and grouped. Subqueries are one of the more advanced topics. Furthermore you find set operations, manipulation of data, creation of database objects and an overview on analytic functions.

Though the code in the book is based on Oracle you can also use it if you work on MS SQL or MySQL databases. For that I’ve added an appendix with the differences between Oracle and each of the other databases. Furthermore you can download a training database with data, a tutorial for setting up a training environment based on oracle (I am planning to do the same for MS SQL and MySQL) and the solutions of the 80+ exercises. These solutions are downloadble for Oracle, MS SQL and MySQL. So you can compare the differences between the different databases.

You can download an excerpt, which consists of

  • Chapter 1 – DB Theory
  • Chapter 3 – Simple queries (except the excersies at the end)
  • Chapter 4 – Queriing multiple tables (only Cross- & Inner Join)

You find the excerpt here: SQL Basics – Learn SQL the easy way – Excerpt

If you enjoy the book I will be happy if you buy it at Amazon. You can select between eBook or paperback. Just simply click on this link: http://a.co/eqnLdbh

If you prefer buying it in your own country-store and not at Amazon.com then you can click on country flags in the sidebar of my blog. You are directly routed to the book page in the specific country store.

Kategorie: MySQL, Oracle SQL, SQL Tutorial |
| April 3, 2017
Keine Kommentare

Oracle External Tables

Hi,

In today’s article I want to explain how to use Oracle’s external tables in order to access flat files in the filesystem. Since Oracle 9i it’s possible to mount files in the file system and make then accessible in the database. Those files appear as normal tables within the database and can be accessed in read-only mode. You cannot write to those files or use indexes on these tables. But you can read data from it and join it with other tables.

My example file (cities.txt) looks like this:

BLOG_0030_PIC01_Example_File

First your administrator (or you) has to create a new directory in the file system and make it accessible within the database. Then the user who should access the external table needs to have read + write rights to this directory. The following two lines are granting both:

CREATE OR REPLACE DIRECTORY ext_file_data AS ‚c:\‘;
 
GRANT read, write ON DIRECTORY ext_file_data TO test;

With these two lines the user TEST will receive read and write access to c:\.

After you’ve created the directory you can define a table with four columns. When you create the table you have to enhance the external table definition and it looks like this:

CREATE TABLE tbl_cities_ext (
  city_name      VARCHAR2(25),
  population      NUMBER,
  country_name  VARCHAR2(25),
  country_code   VARCHAR2(3)
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY ext_file_data
 
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ‚,‘
    MISSING FIELD VALUES ARE NULL
    (
        city_name      CHAR(25),
        population      CHAR(10),
        country_name  CHAR(25),
        country_code   CHAR(3)
    )
  )
  LOCATION (‚cities.txt‘)
)
REJECT LIMIT UNLIMITED;

You can define a lot of things in the external table definition. Normally you use the oracle loader tool but it’s also possible to use data pump. With DEFAULT DIRECTORY you define the directory in which the files are located. Then you can define several access parameters. These parameters define how the data is stored in the files. In my case records are delimited by a new line and fields are terminated by ‘,’. If field values are missing they should be null. Then we have the column list. For each column within the file we have a column in the external table definition. You could also specify fixed column length and column positions and so on. The filenames itself are put in the LOCATION. You can also put more than one filename here separated by comma. This is just a brief introduction. If you need further information on it, just google oracle external files. You find a lot of tutorials there.

When everything was implemented like this then you can start accessing the table with normal SQL operations. This technique can be used to import data to the database.

You find the example code for this in the download section of this article.

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_0030_External_Tables_Examples.zip

Kategorie: Oracle SQL | Tags:, ,
| März 7, 2017
Keine Kommentare

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

Kategorie: Oracle SQL | Tags:, ,
| Februar 27, 2017
Keine Kommentare

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

Kategorie: Oracle SQL | Tags:, ,
| Februar 1, 2017
Keine Kommentare

Macro functions in Cognos Report Studio

Hi,

today I will deal with macro functions. I had a customer who wanted to switch between two dimensions to drill up & down in a dimensional report. That means you have to dynamically change the MUN(=member unique name) for this element to decide whether to use dimension A or dimension B.

The two MUNs for the dimensions looked like this on the highest level:

  1. [Dimensional].[PG].[PG].[Product Group]
  2. [Dimensional].[AX].[AX].[Target Group]

To solve this task I used the following macro functions:

  • Operator +
  • Function substitute (<search pattern>, <replace text>, <orig text>)
  • Function prompt (<p_name>, <data type>, <default>)

To use macro functions you have to define a  macro block like this:  # ….… #. Between the # symbols you will place the macro functions. The macro then defines the expression throughout the macro functions. In our case it will result in one of the two MUNs described above.

Example: You could create a query with a query item that contains the following macro function: # ‘Test1‘+‘Hello‘ #. This will result in the string Test1Hello at runtime in this query item. It’s like you would place this string in the query item by yourself but with macro functions you have the opportunity to dynamically define such expressions.

If we take a look at our example. We’ve create a text: Test1Hello. If we placed this into the query item we would receive an error because of the syntax. We need to put ‘  ‘ around it to  make it a string. For this we can use the macro function sq( … ). Sq stands for single quotes. We will enhance our little example to this: # sq(‘Test1‘ + ‘Hello‘) #. This will result in the string ‘Test1Hello‘ and no error will occur. By the way the operator + concats two strings.

To get back at my customer’s problem. The function substitute(…) will replace the string <search pattern> within the string <orig text> by the string <replace text>. Prompt(…) will ask for parameters and returns the value of a parameter. First argument of this function is the name oft he parameter, second the datatype and third a default value. The third argument is optional. Regarding the datatype. There is the string which is text with single quotes around it and there exists token which is a text too but without single quotes. If you have a prompt page with this parameter it won’t be asked for it again but the value is returned here as well.

With the knowledge of these functions we can now come to the solution for my problem:

# ‘[Dimensional].[‚ +  prompt(‚p_proddim‘, ‚token‘ ,’PG‘)  +  ‚].[‚  +  prompt(‚p_proddim‘, ‚token‘ ,’PG‘)  +  ‚].[‚  +  substitute(‚AX‘, ‚Target Group‘,  substitute(‚PG‘, ‚Product Group‘, prompt(‚p_proddim‘, ‚token‘ ,’PG‘)))  +  ‚]‘   #

The parameter p_proddim contains the selected dimensions (‘PG’ or ‘AX’). First we take the constant text at the beginning (‘[Dimensional.[‘) then we concat the content of p_proddim then the string ‘].[‘ and then again the content of p_proddim. The result is: [Dimensional].[PG].[PG].[ Instead of PG could also be AX depending on the parameter’s selection. The next part is something like an if-then-else. But it’s realized via a nested substitute call. The inner substitute takes the parameter value of p_proddim and checks the value for ‘PG’. If it is found it’s replaced by ‘Product Group’. If not nothing happens. The result of this substitute is the text to be checked in the outer substitute. This one is checked for ‘AX’ and is then replaced by ‘Target Group’.

This is one example how to use macro functions. There are a lot others. In future articles I will deal with macro functions again.

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)!

Kategorie: Cognos Reporting | Tags:, , , ,
« Ältere Beiträge   Neuere Beiträge »