CODEWITHSUNDEEP
sqlsql-servercoalesce
ZMannion
ZMannion

Reputation: 199

COALESCE function won't return CHAR(1)

Using COALESCE function but getting the following error:

Conversion failed when converting the varchar value 'X' to data type int.

I have to join two tables on two conditions. I want that if the second condition doesn't hold but there is a blank cell (not null but blank '') in Table 1 then to join to that row. If the second condition doesn't hold then to return a zero.

Join Table 1 and Table 2 - return Table 2 and column 3 from Table 1.

Table 1

(A, 1, X),
(A, 2, Y),
(A, 3, Z),
(A, , X),
(B, 1, X),
(B, 2, Z),
(B, 3, Y),

Table 2 

(A, 1),
(A, 2),
(A, 3),
(A, 5),
(B, 1),
(B, 2),
(B, 3),
(B, 5)

I want to get a return of 

(A, 1, X),
(A, 2, Y),
(A, 3, Z),
(A, 5, X),
(B, 1, X),
(B, 2, Z),
(B, 3, Y),
(B, 5, NULL)

Code:

DECLARE @table1 TABLE (letter1 CHAR(1), num1 INT, letter2 CHAR(1))  
DECLARE @table2 TABLE (letter1 CHAR(1), num1 INT)  

INSERT INTO @table1 VALUES    
('A', 1, 'X'),  
('A', 2, 'Y'),  
('A', 3, 'Z'),  
('A', null, 'X'),  
('B', 1, 'X'),  
('B', 2, 'Y'),  
('B', 3, 'Z')  

INSERT INTO @table2 VALUES 
('A', 1),  
('A', 2),  
('A', 3),  
('A', 5),  
('B', 1),  
('B', 2),    
('B', 3),  
('B', 5) 

SELECT t2.*,   
    COALESCE(  
        (SELECT TOP 1 letter2 FROM @table1 WHERE letter1 = t2.letter1 AND num1 = t2.num1),   
        (SELECT TOP 1 letter2 FROM @table1 WHERE letter1 = t2.letter1 AND num1 IS NULL),   
        0  
    ) AS missing_letter  
FROM @table2 t2

Upvotes: 2

Views: 1043

Answers (5)

Thom A
Thom A

Reputation: 95574

The problem here is your datatype. COALESCE is short hand for a CASE expression. For example. COALESCE('a',1,'c') would be short hand for:

CASE WHEN 'a' IS NOT NULL THEN 'a'
     WHEN 1 IS NOT NULL THEN 1
     ELSE 'c'
END

The Documentation (COALESCE (Transact-SQL) describes this as well:

The COALESCE expression is a syntactic shortcut for the CASE expression. That is, the code COALESCE(expression1,...n) is rewritten by the query optimizer as the following CASE expression:

CASE  
WHEN (expression1 IS NOT NULL) THEN expression1  
WHEN (expression2 IS NOT NULL) THEN expression2  
...  
ELSE expressionN  
END

A CASE expression follows Data type precedence, and int has a higher datatype precedence than varchar; thus everything will implicit cast to an int. This is why both the COALESCE and CASE expression will fail, because neither 'a' or 'c' can be converted to an int.

You'll need to therefore explicitly CONVERT your int to a varchar: 

COALESCE('a',CONVERT(char(1),1),'c')

The documentation (cited above), however, also goes to state:

This means that the input values (expression1, expression2, expressionN, etc.) are evaluated multiple times. Also, in compliance with the SQL standard, a value expression that contains a subquery is considered non-deterministic and the subquery is evaluated twice. In either case, different results can be returned between the first evaluation and subsequent evaluations.

For example, when the code COALESCE((subquery), 1) is executed, the subquery is evaluated twice. As a result, you can get different results depending on the isolation level of the query. For example, the code can return NULL under the READ COMMITTED isolation level in a multi-user environment. To ensure stable results are returned, use the SNAPSHOT ISOLATION isolation level, or replace COALESCE with the ISNULL function.

Considering you are using a subquery, a (nested) ISNULL might be the better choice here.

It's worth noting, as people seem to confuse them as they are functionally similar, but COALESCE and ISNULL do not behave the same. COALESCE uses Data Type precedence, however, ISNULL implicitly casts the second value to whatever the datatype of the first paramter is. Thus ISNULL('a',1) works fine, but COALESCE('a',1) does not.

Upvotes: 2

LukStorms
LukStorms

Reputation: 29647

The query works if the 0 in the COALESCE is replaced by '0'.
That way the COALESCE doesn't contain mixed data types. 

SELECT t2.*,
    COALESCE(  
        (SELECT TOP 1 letter2 FROM @table1 t1 WHERE t1.letter1 = t2.letter1 AND t1.num1 = t2.num1),   
        (SELECT TOP 1 letter2 FROM @table1 t1 WHERE t1.letter1 = t2.letter1 AND t1.num1 IS NULL),   
        '0'
    ) AS missing_letter  
FROM @table2 t2
ORDER BY t2.letter1, t2.num1;

And you can avoid having to retrieve data from table1 twice.
By using an OUTER APPLY.

Since the expected results has a NULL for ('B',5), the COALESCE isn't even needed this way.

SELECT t2.letter1, t2.num1, t1.letter2 AS missing_letter
FROM @table2 AS t2
OUTER APPLY (
   select top 1 t.letter2
   from @table1 AS t
   where t.letter1 = t2.letter1 
     and (t.num1 is null or t.num1 = t2.num1)
   order by t.num1 desc
) AS t1
ORDER BY t2.letter1, t2.num1;

Result: 

letter1 num1    missing_letter
------- ----    --------------
A       1       X
A       2       Y
A       3       Z
A       5       X
B       1       X
B       2       Y
B       3       Z
B       5       NULL

Upvotes: 0

cloudsafe
cloudsafe

Reputation: 2504

Just change the zero to a null. You can't mix datatypes in a coalesce:

SELECT t2.*,
COALESCE(
(SELECT TOP 1 letter2 FROM @table1 WHERE letter1 = t2.letter1 AND num1 = t2.num1),
(SELECT TOP 1 letter2 FROM @table1 WHERE letter1 = t2.letter1 AND num1 IS NULL),
null
) AS missing_letter
FROM @table2 t2

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269693

If I understand correctly, this has less to do with coalesce() and more to do with the joins:

select t2.*, coalesce(t1.letter2, t1def.letter2) as letter2
from table2 t2 left join
     table1 t1
     on t2.letter1 = t1.letter1 and t2.num1 = t1.num1 left join
     table1 t1def
     on t2.letter1 = t1def.letter1 and t1def.num1 is null;

Upvotes: 2

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Perhaps you need :

select t1.*, t2.*
from table1 t1 outer apply
    ( select top (1) t2.*
      from table2 t2
      where t1.col1 = t.col1 and t1.col2 in ('', t2.col2)
      order by t2.col2 desc
    ) t2;

Upvotes: 2

Related Questions