(+) to LEFT/RIGHT OUTER JOIN

Recently had to transform an Oracle SQL Query into a Standard SQL Query. One of the three steps to take was changing the Oracle notation for outer joins into a "LEFT OUTER JOIN" and "RIGHT OUTER JOIN".

(+) is an Oracle specific function^[1].

Let's go with the old and trivial example of the employees and the departments in a company, before we dive any deeper. It seems to work for everyone else, so it should work here.

As we see if the (+) is on the right of the expression, it is a "LEFT OUTER JOIN" and vice versa.

The example comes straight from [3]. It will show NULL for the department values for all employees that are not assigned to a department. Wikipedia has a nice article on the behaviour of outer joins.^[4]

Multiple Outer Join

This is an example of how you could use left join multiple times.
In my work, what I usually find, is a query that selects from a master table that needs to provide all records, left joined with the rest of the world, just in case the records in other tables do not exist.

This won't work

You can write conditions in the "ON" clause, however, these conditions must be related to the SLAVE table. The following, for example, will not work.^[2]

It will show all employees, even the ones that are no longer working for the company.

The solution here is to move the offending condition into the WHERE clause.

Odd one out

The example below, I encountered, is a bit odd, as it is not really an outer join between tables. It is used here to prevent having to write the 'or is null' part.

References

[1] Oracle (+)

http://docs.oracle.com/cd/B28359_01/server.111/b28286/queries006.htm

[2] The ON condition

http://www.oreillynet.com/pub/a/network/2002/10/01/whatsinacondition.html>

[3] Example Oracle Left Outer Join

http://www.dba-oracle.com/tips_oracle_left_outer_join.htm

[4] Wikipedia JOIN SQL

http://en.wikipedia.org/wiki/Join_%28SQL%29

Outer joins in Oracle

https://blogs.oracle.com/optimizer/entry/outerjoins_in_oracle

A Visual Explanation of SQL Joins

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

DECODE to CASE

Recently had to transform an Oracle SQL Query into a Standard SQL Query. One of the three steps to take was changing the DECODE into a CASE.

DECODE is an Oracle specific function^[1].

The case statements, though more standard, are a bit more wordy than the decode statements. As shown in the following, slightly more complex example.

References

[1] Oracle DECODE

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions040.htm

[2] Case and decode two powerfull constructs of sql t181

http://www.club-oracle.com/forums/case-and-decode-two-powerfull-constructs-of-sql-t181/

Back in the old days, when I needed to run a script upon boot, I could always put it into the /etc/rc.d/rc.local. Apparently all this changed with Fedora Core 14. Fedora Core being my Operating System of choice. The file in question is nowhere to be found.

Granted, this hasn't been an issue, until my gateway decided to quit.

systemd

systemd is the new initialisation system used since Fedora Core 14. It replaces the System V style system of an init process that serially starts all daemons and the like.

We can use "systemctl" and "systemctl status" to display what services started and which failed.

The command "systemctl enable [service]" will enable the service upon startup. It does nothing more, as far as I can tell, than create a symbolic link in the appropriate directory. For example "ln -s '/usr/lib/systemd/system/glassfish3.service' '/etc/systemd/system/multi-user.target.wants/glassfish3.service'".

MySQL Server Script

A MySQL Server systemd service script in Fedora Core 18 is available here.

Installing mysqld on startup of the system is not automatically done in Fedora Core 18. ^[3]

The old way

Run "/etc/init.d/mysqld start". Use "restart" after an update. What should also work is "service mysqld start".
Run "chkconfig --levels 235 mysqld on" to enable the mysql-server on startup.

The new way

Run "systemctl start mysqld.service" to start mysql-server. Use "restart" after an update.
Run "systemctl enable mysqld.service" to enable the mysql-server on startup.

rc.local is back!

Systemd comes automatically with an rc-local.service, It is installed without being enabled. It is available at /usr/lib/systemd/system/rc-local.service.

Here are my steps:

1. create file /etc/rc.d/rc.local (add, for instance, two lines "#!/bin/sh" and "echo Profit!")
2. run "chmod u+x /etc/rc.d/rc.local"
3. run "systemctl enable rc-local.service"
4. run "systemctl start rc-local.service"

The command "systemctl status rc-local.service" should show:

Note

Systemd is in control on which deamons are started when, in what order, etc. This means that forking of your daemon is a bad idea, as it means systemd willl lose control of the process. For example, the process id is no longer known.

References

[1] systemd

http://www.freedesktop.org/wiki/Software/systemd

[2] systemd for Administrators, Part 1

http://0pointer.de/blog/projects/systemd-for-admins-1.html

[3] Install MySQL 5.5.30 on Fedora 18/17, CentOS/Red Hat (RHEL) 6.3/5.9

http://www.if-not-true-then-false.com/2010/install-mysql-on-fedora-centos-red-hat-rhel/

[4] System does not run /etc/rc.local

http://superuser.com/questions/278396/systemd-does-not-run-etc-rc-local

NVL to COALESCE

Recently had to transform an Oracle SQL Query into a Standard SQL Query. One of the three steps to take was changing the NVL into a COALESCE.

NVL is a Oracle specific function^[1].

A simple "search & replace" did the trick.


COALESCE requires the same data types, where NVL does not.

References

Oracle NVL

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions105.htm

Stackoverflow - Oracle differences between nvl and coalesce

http://stackoverflow.com/questions/950084/oracle-differences-between-nvl-and-coalesce