Returning a varchar value from a coalesced int calculation

Question

I'm a newbie learning my way around T-SQL using the AdventureWorks2012 database. I'm using SQL Server 2014, though a solution that would also work with 2008 would be great. I've been given the below exercise:

Write a query using the Sales.SpecialOffer table. Display the difference between the MinQty and MaxQty columns along with the SpecialOfferID and Description columns.

Thing is, MaxQty allows for null values, so I'm trying to come up with a real world solution for an output that doesn't involve leaving nulls in there. However, when I try to use coalesce to return 'No Max' (yes, I get that I could just leave NULL in there but I'm trying to see if I can figure this out), I get the message that the varchar value 'No Max' couldn't be converted to data type int. I'm assuming this is because MaxQty - MinQty as an int takes precedence?

select 
    specialofferid
    , description
    , coalesce((maxqty - minqty),'No Max') 'Qty_Difference'
from 
    sales.specialoffer;

Error:

Msg 245, Level 16, State 1, Line 135
Conversion failed when converting the varchar value 'No max' to data type int.

I thought about just returning a nonsense integer (0 or a negative) but that doesn't seem perfect - if return 0 I'm obscuring situations where the result is actually zero, etc.

Thoughts?

`varchar value 'Null value'`? where would maxqty or minqty be converted to a string? — Marc B, Jul 16 '15 at 17:59
@MarcB: `select coalesce(cast(null as int),'a');` -> `Conversion failed when converting the varchar value 'a' to data type int.` — Andomar, Jul 16 '15 at 18:03
@Andomar: yeah, but there's no `'Null value'` in OP's query... — Marc B, Jul 16 '15 at 18:04
Make sure the types are consistent: `coalesce(cast(maxqty - minqty as varchar), 'No Max')` — sstan, Jul 16 '15 at 18:14

sstan · Accepted Answer · 2015-07-16T19:20:31.380

You just need to make sure that all the parameters of the COALESCE function call have consistent data types. Because you can't get around the fact No Max is a string, then you have to make sure that the maxqty - minqty part is also treated as a string by casting the expression.

select specialofferid
, description
, coalesce(cast(maxqty - minqty as varchar),'No Max') 'Qty_Difference'
from sales.specialoffer;

EDIT: A few more details on the cause of the error

Without the explicit cast, the reason why the COALESCE function attempts to convert the No Max string to an int can be explained by the following documented rule:

Data type determination of the resulting expression is different. ISNULL uses the data type of the first parameter, COALESCE follows the CASE expression rules and returns the data type of value with the highest precedence.

And if you check the precedence of the different types, as documented here, then you will see that int has higher precedence than varchar.

So as soon as you have a mix of data types in the call to COALESCE, SQL Server will try to convert all mismatching parameters to the data type with highest precedence, in this case int. To override that default behavior, explicit type casting is required.

Ahhhh ok. So it isn't that maxqty - minqty was an int taking precedence over the string, it's that the column was going to have int in some case and varchar in others. Very useful, didn't even think of that. Thanks! — Julian Drago, Jul 16 '15 at 18:28
I imagine the first type that happened to result from the query would be interpreted as 'the' type for the generated column. — underscore_d, Jul 16 '15 at 19:01
@underscore_d and OP: The reason why the error happens is clearly documented. I edited my answer to include the relevant information. — sstan, Jul 16 '15 at 19:22

bowlturner · Answer 2 · 2015-07-16T18:21:07.667

0

I would use a case statement to so you can do stuff you want.

select specialofferid
, description
, CASE 
     WHEN  maxqty is null THEN 'No Max'
     ELSE (maxqty - minqty) 'Qty_Difference'
  END
from sales.specialoffer;

edited Jul 16 '15 at 18:21

answered Jul 16 '15 at 18:04

bowlturner

1,968
4
23
35

I can't get that to work, I get the error: The isnull function requires 2 argument(s). When I modify it to isnull(maxqty,'') I get: An expression of non-boolean type specified in a context where a condition is expected, near 'THEN' – Julian Drago Jul 16 '15 at 18:15
I should say I previously tried case and got the same error "Conversion failed when converting the varchar value 'No Max' to data type int.". I did mine a little differently: 'Qty_Different' = case when maxqty is null then 'No Max' when maxqty is not null then (maxqty - minqty) end – Julian Drago Jul 16 '15 at 18:19
@JulianDrago I haven't used isnull for a while. made a change. – bowlturner Jul 16 '15 at 18:19
Why exactly would you say `when -1 = isnull(column, -1)` rather than the dedicated syntax `when column is null`? Not to mention what I said above: a column should only have one type. Your query will create two, if it works at all. – underscore_d Jul 16 '15 at 18:19
@underscore_d thanks, that is actually what I wanted, was thinking to much other dbs.. – bowlturner Jul 16 '15 at 18:20
@JulianDrago, not that it is relevant to `isnull`, but SQL Server does not perform explicit evaluation of boolean expressions, so you have to say `when 1 = booleanExpression`, etc. – underscore_d Jul 16 '15 at 18:23

Returning a varchar value from a coalesced int calculation

2 Answers2