CODEWITHSUNDEEP
sql-servertypes
Basic_
Basic_

Reputation: 25

Error converting data types. CONVERT(money, char(10)'good')?

I am trying to flag where ListPrice > 100 then 'good'.

SELECT          
bom.[ProductAssemblyID]             
,bom.[ComponentID]
,p.[Name]
,p.[ListPrice]

FROM        
[Production].[BillOfMaterials] bom      
right join [Production].[Product] p         
on bom.ProductAssemblyID = p.ProductID

Where 
[ProductAssemblyID] is not null 
and [ComponentID] is not null
and [ListPrice] = (case when [ListPrice]> 100 then (convert(money,char(10),'good')) end )

I get this error, yet my code has nothing to do with varchar()

Msg 8116, Level 16, State 1, Line 16 Argument data type varchar is invalid for argument 3 of convert function.

Upvotes: 0

Views: 108

Answers (2)

A_Sk
A_Sk

Reputation: 4630

First of all Read CAST and CONVERT 

(convert(money,char(10),'good')) is invalid.

because you are trying to convert 'good'(a string value) to money(acceptable decimal/integer value)

So, you need to try:

  SELECT          
    a.[ProductAssemblyID]             
    ,a.[ComponentID]
    ,b.[Name]
    ,b.[ListPrice]
    , CASE
        WHEN b.ListPrice > 100 THEN 'higher than 100'
        ELSE 'less than 100'
    END AS result

FROM [Production].[BillOfMaterials] a
RIGHT JOIN [Production].[Product] b         
ON a.ProductAssemblyID = b.ProductID

WHERE a.[ProductAssemblyID] is not null 
AND a.[ComponentID] is not null

Upvotes: 1

TZHX
TZHX

Reputation: 5377

I think you want to make your "flag" a column in your SELECT, not a portion of your WHERE clause, like so:

SELECT          
    bom.[ProductAssemblyID]             
    ,bom.[ComponentID]
    ,p.[Name]
    ,p.[ListPrice]
    , CASE
        WHEN p.ListPrice > 100 THEN 'GOOD'
        ELSE ''
    END AS 'flag'

FROM [Production].[BillOfMaterials] bom         
RIGHT JOIN [Production].[Product] p         
ON bom.ProductAssemblyID = p.ProductID

WHERE [ProductAssemblyID] is not null 
AND [ComponentID] is not null

Upvotes: 2

Related Questions