(+) 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.

	SELECT
	last_name,
	department_name
	FROM
	employees e,
	departments d
	WHERE
	e.department_id = d.department_id(+);
	-- changed to
	SELECT
	last_name,
	department_name
	FROM
	employees e
	LEFT OUTER JOIN
	departments d
	ON
	e.department_id = d.department_id;

view raw oracle_to_outer_join2.sql hosted with ❤ by GitHub

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.

	-- show all employees, including their departments,
	-- contact information, jobtitle, salary etc.
	SELECT
	e.last_name,
	e.salary,
	e.scale,
	d.department_name,
	c.description,
	j.title,
	j.stdsalary
	FROM
	employees e,
	departments d,
	contacts c,
	jobs j
	WHERE
	e.department_id = d.department_id(+)
	AND d.deleted(+) = false
	AND e.id = c.id(+)
	AND e.jobtitle = j.title(+)
	AND e.left_company = false
	-- changed to
	SELECT
	e.last_name,
	e.salary,
	e.scale,
	d.department_name,
	c.description,
	j.title,
	j.stdsalary
	FROM
	employees e
	LEFT OUTER JOIN
	departments d
	ON
	e.department_id = d.department_id
	AND d.deleted = false
	LEFT OUTER JOIN
	contacts c
	ON
	e.id = c.id
	LEFT OUTER JOIN
	jobs j
	ON
	e.jobtitle = j.title
	WHERE
	e.left_company = false

view raw complex_outer_join.sql hosted with ❤ by GitHub

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]

	SELECT
	last_name,
	department_name
	FROM
	employees e
	LEFT OUTER JOIN
	departments d
	ON
	e.department_id = d.department_id
	AND e.left_company = false;

view raw outer_join_with_funny_on_clause.sql hosted with ❤ by GitHub

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.

	-- provides all employee that are not higher management
	-- includes employees without a job title
	SELECT * FROM employees
	WHERE jobtitle (+) not in ('manager', 'department head', 'president')
	-- changed to
	select * from employees
	where jobtitle not in ('manager', 'department head', 'president')
	or jobtitle is null

view raw oracle_to_outer_join.sql hosted with ❤ by GitHub

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

	-- provides percentage taxation
	DECODE (tax.taxtype, 'VAT', tax.vat, tax.general) TAX,
	-- changed to
	CASE tax.taxtype
	WHEN 'VAT' THEN tax.vat
	ELSE tax.general
	END TAX,

view raw decode_to_case_1.sql hosted with ❤ by GitHub

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

	-- provides percentage taxation
	DECODE(tax_notice.taxtype ,'VAT',NVL(tax.vat, tax.general)
	,'PROP',NVL(tax.property_tax, tax.general)
	,'INC',NVL(tax.income_tax, tax.general)
	,'TAR',NVL(tax.tariff, tax.general)
	,tax.general)
	-- changed to
	CASE tax_notice.taxtype
	WHEN 'VAT' THEN COALESCE(tax.vat, tax.general)
	WHEN 'PROP' THEN COALESCE(tax.property_tax, tax.general)
	WHEN 'INC' THEN COALESCE(tax.income_tax, tax.general)
	WHEN 'TAR' THEN COALESCE(tax.tariff, tax.general)
	ELSE tax.general
	END

view raw decode_to_case_2.sql hosted with ❤ by GitHub

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.

	# It's not recommended to modify this file in-place, because it will be
	# overwritten during package upgrades. If you want to customize, the
	# best way is to create a file "/etc/systemd/system/mysqld.service",
	# containing
	# .include /lib/systemd/system/mysqld.service
	# ...make your changes here...
	# For more info about custom unit files, see
	# http://fedoraproject.org/wiki/Systemd#How_do_I_customize_a_unit_file.2F_add_a_custom_unit_file.3F
	# For example, if you want to increase mysql's open-files-limit to 10000,
	# you need to increase systemd's LimitNOFILE setting, so create a file named
	# "/etc/systemd/system/mysqld.service" containing:
	# .include /lib/systemd/system/mysqld.service
	# [Service]
	# LimitNOFILE=10000
	# Note: in F-17 and beyond, /usr/lib/... is recommended in the .include line
	# though /lib/... will still work.
	[Unit]
	Description=MySQL database server
	After=syslog.target
	After=network.target
	[Service]
	Type=simple
	User=mysql
	Group=mysql
	ExecStartPre=/usr/libexec/mysqld-prepare-db-dir %n
	# Note: we set --basedir to prevent probes that might trigger SELinux alarms,
	# per bug #547485
	ExecStart=/usr/bin/mysqld_safe --basedir=/usr
	ExecStartPost=/usr/libexec/mysqld-wait-ready $MAINPID
	# Give a reasonable amount of time for the server to start up/shut down
	TimeoutSec=300
	# Place temp files in a secure directory, not /tmp
	PrivateTmp=true
	[Install]
	WantedBy=multi-user.target

view raw mysqld.sh hosted with ❤ by GitHub

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:

	[root@dhcppc1 etc]# systemctl status rc-local.service
	rc-local.service - /etc/rc.d/rc.local Compatibility
	Loaded: loaded (/usr/lib/systemd/system/rc-local.service; static)
	Active: active (exited) since Tue 2013-02-12 21:53:03 CET; 1min 33s ago
	Process: 2717 ExecStart=/etc/rc.d/rc.local start (code=exited, status=0/SUCCESS)
	Feb 12 21:53:03 dhcppc1 systemd[1]: Starting /etc/rc.d/rc.local Compatibility...
	Feb 12 21:53:03 dhcppc1 rc.local[2717]: Profit!
	Feb 12 21:53:03 dhcppc1 systemd[1]: Started /etc/rc.d/rc.local Compatibility.
	[root@dhcppc1 etc]#

view raw output_systemd.txt hosted with ❤ by GitHub

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.

	-- provides an employees mobile phonenumber or his/her
	-- regular phonenumber if he/she doesn't have one
	nvl(empl.mobile_phonenumber, empl.phonenumber)
	-- changed to
	coalesce(empl.mobile_phonenumber, empl.phonenumber)

view raw nvl_to_coalesce.sql hosted with ❤ by GitHub

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