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;
Advertisements

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

  1. ashif says:

    hai,friends i have one doubt in sql here i mentioned that please help me.

    COLUMN NAME1 COLUMN NAME2

    A 1

    A 2

    A NULL

    B 3

    C NULL

    this is my table from that i have to select that not null values of A and B and also select NULL value of C.that is if A having NULL values only and C having NOT NULL values only means I have to select NULL values of A and not null values of C.that is if A having both NULL and NOT NULL values means i want NOT NULL only of A and C having NULL values means i want NULL values of C.at the same time if C having NULL AND NOT NULL value means i want the NOT NULL values of C.please help me soon.am waiting

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: