CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
Caffeinated
Caffeinated

Reputation: 12484

In Oracle SQL, how do I only add text to a column only if it is not null?

I have a column in my query like this :

ROUND(( xyz.DATE - abc.DATE), 5)|| ' days' AS "Average SOD Approval duration"

The problem is that , when my data is null, I get 'days' printed in each null box. I want there to be nothing in the output , if it's null. and only append "days" when there is data.

Upvotes: 3

Views: 2053

Answers (4)

Boneist
Boneist

Reputation: 23588

Use a case statement:

CASE WHEN ROUND((xyz.date - abc.date), 5) IS NOT NULL 
          THEN ROUND((xyz.date    - abc.date), 5)||' days'
END

You could add in an "ELSE NULL" clause to make it doubly clear that if the conditions aren't met then null would be returned if you wanted to make the case statement especially explicit, but since that's the default, it's not necessary.

Upvotes: 1

Ponder Stibbons
Ponder Stibbons

Reputation: 14858

Here is shorter way:

select ltrim(round(d1-d2, 5)|| ' days', ' days') dt from test

SQLFiddle

Edit: If at least one of dates is null, date difference is also null. After concatenation of " days" you get only this string as value, so trimming phrase " days" from left side gives null. If dates are not null trimming from left has no impact. Documentation of function ltrim(). In this case with trimming you can shorten syntax and avoid repeated calculations.

Upvotes: 4

Matt
Matt

Reputation: 15061

Use a CASE statement

SELECT CASE WHEN ROUND(( xyz.DATE - abc.DATE), 5) IS NULL 
            THEN NULL 
            ELSE ROUND(( xyz.DATE - abc.DATE), 5)|| ' days'
            END AS "Average SOD Approval duration"
FROM yourtablename

Upvotes: 2

miracle_the_V
miracle_the_V

Reputation: 1166

I'd suggest NVL2 instead of case

SELECT nvl2(ROUND((xyz.DATE - abc.DATE), 5),
            ROUND((xyz.DATE - abc.DATE), 5) || 'days',
            NULL) AS "Average SOD Approval duration"
FROM dual;

Upvotes: 4

Related Questions