Handling the EMPTY/NULL value in the select query

Database
Fan

Handling the EMPTY/NULL value in the select query

I am trying to get a 0 value returned when execute the below query if my condition is not satisfied. I tried running in SQL assistance and returned nothing for null/empty condition

SELECT CASE WHEN TRIM(XXXX) IS NOT NULL OR TRIM(XXXX)<>''
THEN XXXX
ELSE 0
END
as "XXXXX"
FROM W_XXXXXX
WHERE TR_XXX= 'xxxx'

Ideally I should get 0 as the returned value if else condition is met. There will be no record for TR_XXX= 'xxxx'
8 REPLIES
Junior Supporter

Re: Handling the EMPTY/NULL value in the select query

>>"There will be no record for TR_XXX= 'xxxx'"

If there is no row to apply the CASE, How can it be applied?

Once again: please post the EXACT SQL and the CREATE TABLE (and the error, if any)

Cheers.

Carlos.
Enthusiast

Re: Handling the EMPTY/NULL value in the select query

one ugly approach is to use UNION to select a hardcoded value which you won't expect in your normal results e.g. -1, then use your case approach. e.g.

SELECT CASE WHEN u.val = -1 then 0 else val end
from
(
SELECT val ..... [your original query]
UNION
SELECT -1 as val
) u
Fan

Re: Handling the EMPTY/NULL value in the select query

To make is simple.

SELECT CASE WHEN TRIM(COL1) IS NOT NULL OR TRIM(COL1)<>''
THEN COL1
ELSE 0
END
FROM W_TABLE
WHERE DB_NAME= 'tera'

In the table there is only 1 record which has the DB_NAME='tera'. If I run the same query it is working fine as expected and giving the COL1 value.

But when I run DB_NAME='mysql' since there is no record in the table I am not getting no value. Based on the above query I am expecting a 0 value for this condition
Junior Supporter

Re: Handling the EMPTY/NULL value in the select query

You are confused about two different things: NULL value and 'no rows returned'.

NULL is about columns: the value for a column in some returned rows is NULL, thus you can apply CASE, ask if it IS NULL and so on.

Another different thing is when a SELECT returns no rows. If there is 'no rows returned', there are no columns and no values (NULL or NOT NULL) to play with.

In your case you are in the second point.

Anyway, you could get the desired results with something like:

SELECT CASE
WHEN TRIM(b.COL1) IS NOT NULL OR TRIM(b.COL1)<>'' THEN b.COL1
ELSE '0'
END THE_COLUMN
FROM
(SELECT 1 DUMMYCOL) a
LEFT OUTER JOIN W_TABLE b
ON b.DB_NAME= 'tera'
;

HTH.

Cheers.

Carlos.

Fan

Re: Handling the EMPTY/NULL value in the select query

It works. Thanks CarlosAL. I Appreciate.

Re: Handling the EMPTY/NULL value in the select query

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

Enthusiast

Re: Handling the EMPTY/NULL value in the select query

Read your post couple of time but couldn't understand what exactly you are looking for. I think you need to explain your query with bit more clarity!

Enthusiast

Re: Handling the EMPTY/NULL value in the select query

In simple terms using merge statement i dont want to roll back good records when any failure occurs.

my merge statement should let all good records continue to insert  and log all error records in error table.