CODEWITHSUNDEEP
sql
user7978660
user7978660

Reputation:

Nested Case Statement for multiple condition

I have a data set like bellow image. I need in the data set
Grade Red is present then in OpenSegment=yes if not but Grade Amber is present then in OpenSegment=yes if not but Grade Blue is present then in OpenSegment=yes

for that I write 

 COALESCE(
                  CASE WHEN airag.FK_MasterRAGRatingID=1 THEN 'yes' ELSE '' END,
                  CASE WHEN airag.FK_MasterRAGRatingID=2 THEN 'yes' ELSE '' END,
                 CASE WHEN airag.FK_MasterRAGRatingID=4 THEN 'yes' ELSE '' END
                )

but not working.

enter image description here

Result after putting ELSE NULL

enter image description here

Upvotes: 0

Views: 1357

Answers (3)

DineshDB
DineshDB

Reputation: 6193

Try this, COALESCE is used for First Not NULL Value, if you give EMPTY('') in the else part, it consider the column has some value.

So you have to use NULL instead of ''.

COALESCE(
                  CASE WHEN airag.FK_MasterRAGRatingID=1 THEN 'yes' ELSE NULL END,
                  CASE WHEN airag.FK_MasterRAGRatingID=2 THEN 'yes' ELSE NULL END,
                 CASE WHEN airag.FK_MasterRAGRatingID=4 THEN 'yes' ELSE NULL END
                )

IF we want Empty in the NULL columns, we have to use like below:

ISNULL(COALESCE(
                  CASE WHEN airag.FK_MasterRAGRatingID=1 THEN 'yes' ELSE NULL END,
                  CASE WHEN airag.FK_MasterRAGRatingID=2 THEN 'yes' ELSE NULL END,
                 CASE WHEN airag.FK_MasterRAGRatingID=4 THEN 'yes' ELSE NULL END
                ),'')

Hope this is what you need:

SELECT ISNULL(CASE WHEN airag.FK_MasterRAGRatingID=1 THEN 'yes' 
        ELSE  
         CASE WHEN airag.FK_MasterRAGRatingID=2 THEN 'yes'
         ELSE
          CASE WHEN airag.FK_MasterRAGRatingID=4 THEN 'yes' ELSE NULL END END  END
,'')

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522817

I don't even think you need three separate CASE expressions here:

SELECT
    CASE WHEN airag.FK_MasterRAGRatingID IN (1, 2, 4) THEN 'yes' END AS OpenSegment
    -- other columns
FROM yourTable

The above logic should work whether or not the table you showed us is derived.

The default ELSE condition for a CASE expression is NULL. Hence the above would label open segments of non matching rows as NULL. If you want to use a different value, then provide an explicit ELSE:

CASE WHEN airag.FK_MasterRAGRatingID IN (1, 2, 4) THEN 'yes' ELSE '' END

This would assume you want to use empty string, but it could be any value.

Upvotes: 4

A. van Esveld
A. van Esveld

Reputation: 258

Good answer by Tim Biegeleisen but looking at the question shouldn't you do the case when on the Grade column instead of ID?

So code would be;

SELECT
    CASE WHEN airag.Grade IN ('Red', 'Amber', 'Blue') THEN 'yes' END AS OpenSegment
    -- other columns
FROM yourTable

Upvotes: 1

Related Questions