ISNULL in a Case When

1

I am doing a select using CASE WHEN in Sql Server, so it is done checking the existence of a record, if it exists, it makes select in a table, otherwise it makes select in another table, however, the two can return null. Something like this:

CASE WHEN EXISTS(SELECT Id FROM TabelaA WHERE ColA = 10)
    THEN
        (SELECT ColA from TabelaA WHERE ColA = 10)
    ELSE
        (SELECT ColA from TabelaB WHERE ColB = 10)
END Column

How to use a ISNULL in this script to avoid a null return of both selects?

    
asked by anonymous 24.10.2017 / 21:21

2 answers

1

I solved the problem in a simple way, (I was surprised, I did not know it worked):

 ISNULL(CASE WHEN EXISTS(SELECT Id FROM TabelaA WHERE ColA = 10)
          THEN
             (SELECT ColA from TabelaA WHERE ColA = 10)
          ELSE
             (SELECT ColA from TabelaB WHERE ColB = 10)
        END, '') Column
    
24.10.2017 / 21:33
1

Another simpler form of resolution would be to use COALESCE The advantage of coalesce is that you can provide a list of parameters and the first non-null is displayed

COALESCE(A,B,C,D..Z)

If A is null then evaluate B, if B is null then evaluate C, etc.

The code proposition to eliminate the case structure:

Select Coalesce ((SELECT ColA from TabelaA WHERE ColA = 10), (SELECT ColA from TabelaB WHERE ColB = 10)) 

See an example working in SQL FIDDLE

    
29.07.2018 / 04:32