CODEWITHSUNDEEP
sqlsql-servert-sql
Gray Meiring
Gray Meiring

Reputation: 421

Adding text string to CASE Statement

I am using the following SQL CASE:

SELECT 
BomMast.BomStockCode
, BomMast.BomDescription
, CASE
           WHEN StkItem.AveUCst <= 0 THEN 'ERROR' 
           WHEN StkItem.AveUCst > 0 THEN (StkItem.AveUCst * BomComp.ProductionQty) 
        END AS TotalCost
FROM BomComp
INNER JOIN BomMast
ON BomMast.BomID = BomComp.BomMasterKey
INNER JOIN StkItem
ON StkItem.StockLink = BomComp.ComponentStockLink

But I get the following message:

Msg 8114, Level 16, State 5, Line 2 Error converting data type varchar to float.

Am I not allowed to add test within the CASE statement?

Thank you!

Upvotes: 0

Views: 14248

Answers (3)

tomrumour
tomrumour

Reputation: 76

Yes, text can be used as the result a case statement, as can any datatype, but each case must return the same type, as the results column must have one type only.

Your [TotalCost] column has conflicting data types. [StkItem.AveUCst] is a float and the literal value of 'ERROR' is a varchar. If you are intending to retain the benefits of number-based value in your results column, consider replacing 'ERROR' with the SQL keyword NULL.

Upvotes: 0

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20489

Change your query to: 

SELECT BomMast.BomStockCode
    ,BomMast.BomDescription
    ,CASE 
        WHEN StkItem.AveUCst <= 0
            THEN 'ERROR'
        WHEN StkItem.AveUCst > 0
            THEN CAST((StkItem.AveUCst * BomComp.ProductionQty) AS NVARCHAR(MAX))
        END AS TotalCost
FROM BomComp
INNER JOIN BomMast ON BomMast.BomID = BomComp.BomMasterKey
INNER JOIN StkItem ON StkItem.StockLink = BomComp.ComponentStockLink

The datatypes of the values you want to show in either branches of your CASE statements need to be the same in order to work.

Edit:

After @underscore_d's suggestion, I also consider that it would be a far better option to display NULL instead of the message ERROR and then handle this NULL value in the application level.

Hence, your case statement will change to:

CASE 
    WHEN StkItem.AveUCst <= 0
        THEN NULL
    WHEN StkItem.AveUCst > 0
        THEN (StkItem.AveUCst * BomComp.ProductionQty)
END AS TotalCost

Upvotes: 1

user4622594
user4622594

Reputation:

Your column TotalCost (neither any other column) can be a type-mixture. In first case it would be a varchar, in second case it would be floator something like that. THAT IS NOT POSSIBLE.

Upvotes: 0

Related Questions