Calculating a value whether one column of several is not null in PL/SQL

I wanted to calculate in a SQL query whether one of three columns in a table was not null. I did not want to do it in the Java code to keep code clean.

First, I started to used DECODE and NVL2 to reproduce a kind of IF-THEN-ELSE to lazily compare my values:

IF <field one> IS NOT NULL THEN true ELSE IF <field two> IS NOT NULL THEN true ELSE IF <field three> IS NOT NULL THEN true ELSE FALSE

This was a bit convoluted and resulted in the following code:

SELECT DECODE(NVL2(remarks_f, 'true','false'), 'true', 'true','false',DECODE(NVL2(remarks_n, 'true','false'), 'true', 'true','false',DECODE(NVL2(remarks_d, 'true','false'), 'true', 'true','false','false'))) AS hasRemarks
FROM bulletin;

After discussion with a colleague of mine, he suggested that I concatenated the values of the three fields and check whether the outcome was null or not like:

IF <field one> + <field two> + <field three> IS NOT NULL THEN true ELSE FALSE

This resulted in the following code:

SELECT DECODE(NVL2(remarks_f || remarks_n || remarks_d, 'true','false'), 'true', 'true','false','false') AS hasRemarks
FROM bulletin;

Then, I realised that NVL2 was more than enough to achieve what I wanted and I ended up with the following code:

SELECT NVL2(remarks_f || remarks_n || remarks_d, 'true','false') AS hasRemarks
FROM bulletin;

Installing Oracle SQL Developer on Ubuntu

Update: As I switched to Linux Mint, I ran into an permission problem. To have it working, follow the procedure available at, which provides additional steps.

On my current project, we use Oracle database. The best free tool I have found so far to work with the database as a Java developer is Oracle SQL Developer.

Oracle does not provide a package for Debian based distros. I tried to run the tool from the generic archive but it failed to run because it seems to be aiming at another environment with respect to the Swing look and feel that it failed to load.  After some googling, I found that there is a package to make a package (sqldeveloper-package)out of the archive to make it installable as such.

Here is how to use it.

Download Oracle SQL Developer

As the download requires that one have an Oracle account, I download it from the browser at the following address:

Install Java

See my post Install Sun JDK 6 on Ubuntu 11.10

Install the sqldeveloper-package and its dependencies

$ sudo apt-get install sqldeveloper-package debhelper

 Install dos2unix

$ sudo apt-get install tofrodos

It is necessary to create the following symlinks for the tool to work:

$ sudo ln -s fromdos dos2unix
$ sudo ln -s todos unix2dos

Make the deb package

It seems that the -b switch can be used to indicate where to generate the .deb but it does not seem to work (Or I did not spend enough time trying to get it to work). The tool will generate the .deb in the working directory.

$ cd ~/Downloads
$ make-sqldeveloper-package ~/Downloads/

Install the package

$ sudo dpkg -i sqldeveloper_3.1.06.44+0.2.3-1_all.deb

The tool is now available in Applications->Programming->Sql Developer

%d bloggers like this: