Archiv für September 28, 2016

String Comparison Differences ( MySQL vs. Oracle)

When comparing strings in MySQL you find out that something is different compared with Oracle. Basicly said it’s that Oracle is case-sensitive when comparing strings and MySQL is not. Let’s say you have a table TBL_TEST that looks like this:

TBL_TEST
ID Name City
1 Schmidt kiel
2 Maier KIEL

 

Given the following SQL:

SELECT *
FROM  TBL_TEST
WHERE City=‘kiel‘;

In MySQL you receive both rows as the string comparison is not case-sensitive. In Oracle and MS SQL you would receive only the first row. It is the same if you use the LIKE operator.

So how will you accomplish a case sensitive comparison in MySQL then?

The trick is to use the operator binary in your equation. That converts a normal string into a binary string and the result is that the strings are compared by character code rather than by character. The SQL changes like this:

SELECT *
FROM  TBL_TEST
WHERE City=binary ‘kiel‘;

Now in MySQL you also only receive the first row. The binary operator can be placed on both sides, the effect is always the same. By having one binary string in your equation the strings are compared on byte level.

I hope this helps and explains this behaviour a little bit.

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

Share and Enjoy: Diese Icons verlinken auf Bookmark Dienste bei denen Nutzer neue Inhalte finden und mit anderen teilen können.
  • LinkedIn
  • XING
  • Facebook
  • Google Bookmarks
  • Twitter

Ask me questions!

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.

Thanks in advance!

Fabian

Share and Enjoy: Diese Icons verlinken auf Bookmark Dienste bei denen Nutzer neue Inhalte finden und mit anderen teilen können.
  • LinkedIn
  • XING
  • Facebook
  • Google Bookmarks
  • Twitter

How to generate a list of dates

Hi,

it’s been a long time since the my last article in this BLOG. But from today on I want to reactivate my blog. I’ll start with an article on how to generate a list of rows, e.g. if you want to generate a sequential list of dates between two given dates (e.g. 1st of January, 2015 and 31st of December 2015). You have the need to generate 365 rows and each is incremented by one day.

To do that within Oracle SQL you can just use the concepts of hierarchical queries, in detail that’s the connect by statement which just tells the dbms in a hierarchical query how the different levels are connected (i.e. a child and parent key). In our case we will use it to generate a new row for each level and the level in our case is the counter for each row. We are connecting every row to a parent until we reach a hierarchy depth of e.g. 365. That’s why it is working in this case.

Here comes the code:

SELECT (to_Date(’01.01.2015′, ‘DD.MM.YYYY’) + (LEVEL -1)) AS GEN_DATE
FROM DUAL
CONNECT BY LEVEL <=to_date(’31.12.2015′, ‘DD.MM.YYYY’)-   to_date(’01.01.2015′, ‘DD.MM.YYYY’)+1;

With the expression to_date(’31.12.2015′, ‘DD.MM.YYYY’)-to_date(’01.01.2015′, ‘DD.MM.YYYY’) we just tell the database that we want to generate  the dates between the given dates. If you subtract two dates you receive the difference in days. We need one more otherwise the last given date is missing. In the select we just take the given start date and the level is added. As the level counts up by one with each new line we always add one more in each line. Adding a number to a date is just adding a day. And in this way we generate a list of the dates between two given dates.

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

Share and Enjoy: Diese Icons verlinken auf Bookmark Dienste bei denen Nutzer neue Inhalte finden und mit anderen teilen können.
  • LinkedIn
  • XING
  • Facebook
  • Google Bookmarks
  • Twitter