## Cross Join

Hi,

today’s article is about Cross Joins and the few cases where you can use it.

In general a cross join just takes all rows from table A and combines them with all rows from table B. Other terms for cross join are cross product and cartesian product. Let’s make a small example. We need two tables:

TBL_A

TBL_B

Now you can do the CROSS JOIN with the following SQL:

 SELECT * FROM TBL_A CROSS JOIN TBL_B

Compared with an inner join you write CROSS JOIN and you leave the ON clause. That one is not needed as you don’t define any joining condition. That is because all rows from A are taken and combined with all rows of B. The result looks like this:

As you can see the row one from table A (1 / A) is taken and combined with all three rows from table B. The result are three rows where the first two columns (that come from table A) are the same and the last two columns (that come from table b) are changing. The same is repeated with row two from table A (2 / A).

So you can calculate the number of resulting rows with  rows in table A * rows in table B. In our example it’s 2*3 = 6 rows.

This type of join is seldom used as it makes sense only in a few cases. For example if you have a value per week and you want to distribute it on each day of the week. I have another table for that:

Now we run the following query:

 SELECT WEEK_START_DATE+DAY_OF_WEEK AS DAY_DATE,             WEEK_VALUE FROM TBL_VALUE_WEEK     CROSS JOIN (                         SELECT LEVEL-1 AS DAY_OF_WEEK                         FROM dual                         CONNECT BY LEVEL<=7                        ) week_days ORDER BY DAY_DATE

And the result looks like this:

As you can see we have all seven days of the week starting with 2017-12-04 and each day has the WEEK_VALUE of this week. Then comes the next week.

Let’s take a closer look at the query. We have a subquery which is needed for generating the seven days of a week. This is database specific. In this case it is an opportunity on an Oracle database. You could also create a table with the numbers 0 to 7 and work with that instead of a subquery. If you are working on other databases you have to change that part.

Besides the subquery we are just accessing the weekly table cross joining it so in a result we get seven rows a week instead of one.

If you are interested in further information on SQL just check out my BLOG or take a look at my new SQL book. I’ve added a free excerpt on this BLOG as well. You can also attend training on my corporate homepage. At the moment my classroom trainings are only available in German. Inhouse training and online courses are also available in English language. So check out my trainings (German) or my online courses (English)!

BLOG_0032_Cross_Join_Testdate_Examples.zip

## Analytical Functions IV: RANGE vs. ROWS

Hi,

today’s article is the fourth part of my tutorial on analytical functions. Today I will deal with the differences between RANGE and ROWS windows. We already learned about the ROWS windows in Part I of this tutorial. Today we will take closer look at the RANGE windows and how they differ from ROWS.

It contains DAYs from 01/01/2017 till 30/04/2017 and PRODUCT_NO from 1 to 3.

Now let’s take a look at the first example. We will create a query to calculate the monthly sums of the turnover. Then we want to compare the previous and the current month. We could do it for example with window functions.

 SELECT MONTH,             TURNOVER AS CUR_MONTH,             SUM(TURNOVER) OVER (ORDER BY MONTH                                                     ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING                                                    ) AS PREV_MONTH FROM (           SELECT DISTINCT                      to_char(DAY, ‚YYYY-MM‘) AS MONTH,                      SUM(TURNOVER) OVER (PARTITION BY to_char(DAY, ‚YYYY-MM‘)                                                         ) AS TURNOVER           from tbl_test      )

What we are doing here is calculating monthly values first. Then we are working with a ROWS window, which means that for each row (that is returned by the sub query) we calculate a separate window which is defined in this case as ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING. That means that we are just accessing the previous row. For that it is important that we first sort the data what is done by ORDER BY MONTH.

ROWS also means that the window definition is based on physical rows. That’s the reason why we need a sub query. First we have to create “physically” monthly rows. Then we can work with them. If we don’t do it we receive strange results as the physical level would still be on day level. So within the window daily data is accessed and shown.

Now if we use the RANGE instead of the ROWS we don’t need the subquery. I.e. we receive the same result with this:

 SELECT DISTINCT            to_char(DAY, ‚YYYY-MM‘) AS MONTH,            SUM(TURNOVER) OVER (PARTITION BY to_char(DAY, ‚YYYY-MM‘)                                              ) AS CUR_MONTH,            SUM(TURNOVER) OVER (ORDER BY to_number(to_char(DAY, ‚YYYYMM‘))                                                RANGE BETWEEN 1 PRECEDING AND 1 PRECEDING                                              ) AS PREV_MONTH FROM tbl_test

Test it yourself. The results are the same. There are two differences. We are using RANGE instead of ROWS and we don’t have a sub query. We don’t need the sub query any longer because the RANGE windows are based on logical blocks or logical value changes. When you take a closer look at the ORDER BY clause in the last example you find that we are not using to_char(…..) but to_number(to_char(….)).

We do that as the range windows only works with dates or numbers in the ORDER BY clause. The expression you are using there is important for that logical value change. All rows with the same value in that expression are treated as one. They are summed up (because we are using SUM) first (that’s what we have done manually with the sub query in the first example) and afterwards the window is defined. If we say one block back we are stepping back one logical change. In our case it would mean that we step back one month and not one row.

I know that is a little difficult to understand. ROWS means we are defining our window based on physical row changes. RANGE means we are defining it by logical value change within the ORDER BY expression.

That also leads to another point. If you use ROWS and do your window on physical rows the database doesn’t really care if the previous month isn’t available. If we take April and March isn’t there the February is taken instead. With RANGE it is different. Here a NULL would be shown for March as the previous month of April doesn’t have a value. Which is correct then.

This was just a small introduction to the RANGE windows. If you are interested in further information on that just check out my BLOG. Maybe I will write another article on that with more examples. Or take a look at my new SQL book. I’ve added a free excerpt on this BLOG as well.

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 is also available in English language. So check out my trainings (German) or my online courses (English)!

BLOG_0031_ROWS_RANGE_Examples.zip

## 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.

• 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.

Hi,

as I’ve decided to continue my BLOG I just wanted to ask you guys, if you have any topics I should write an article on. Just let me know: fabian@gaussling.com.

If you have urgent questions in your daily work or if you have things that you always wanted to know, just let me know. I will select some or maybe even all of the questions and topics and create articles on it.

The general area of topics should be: Databases, Datawarehouse, Business Intelligence, ETL, Cognos, SQL, Oracle.

Fabian

## Oracle’s Virtual Columns

Since the version 11g Oracle supports so called virtual columns. Virtual columns consist of a calculation depending on other columns in a table. They are not stored persistently in the tablespace but calculated during run time. These virtual columns can be useful sometimes. For example if you want to implement interval partitioning on numbers in a varchar column (Oracle only supports interval partitioning on date or number columns). Or if you want to enhance an existing table easily or for testing reasons by new calculation without editing the complete table loading routine.

In order to build a virtual column in a table you easily add it to the CREATE TABLE statement as in the following example:

 CREATE TABLE Test(                 ID               NUMBER,                 QTY              NUMBER(5,0),                 PRICE            NUMBER(10,2),                 TURNOVER        NUMBER GENERATED ALWAYS AS (QTY*PRICE) VIRTUAL );

As you can easily see in this example the turnover column is a virtual column. During run time Oracle calculates the turnover by multiplying the quantity by the price.

If you want to insert new values you have to keep in mind that you cannot insert values in virtual columns. For that you have to explicitly define the relevant columns when inserting datasets:

 INSERT INTO Test (ID, QTY, PRICE) VALUES (1, 10, 5.75);

There are some other restrictions:

• Virtual columns can’t be based on other virtual columns, i.e. only on “normal” columns
• If the virtual column’s expression contains a user defined functions, it can’t be used as a partitioning key.

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