CODEWITHSUNDEEP
sqlsql-server
Meital
Meital

Reputation: 43

Operand data type varchar is invalid for divide operator

I've just started using SQL and I have a pretty basic question:

I'm tried dividing 2 columns (amount/rate) - I converted them from 'money' to 'INT' but when I tried executing it gave me this error:

Operand data type varchar is invalid for divide operator.

This is the query I typed:

select referenceid, 
       CONVERT(decimal(15,3), sellamount) as 'amount', 
       CONVERT(decimal(15,3), rateactual) as 'Rate',
       CONVERT(decimal(15,3), 'amount' / 'rate') as 'local amount'
FROM currencyfxconversions

Can someone help me understand what I did wrong?

Upvotes: 2

Views: 18048

Answers (3)

StackUser
StackUser

Reputation: 5398

Try like this,

SELECT referenceid
    ,CONVERT(DECIMAL(15, 3), sellamount) AS 'amount'
    ,CONVERT(DECIMAL(15, 3), rateactual) AS 'Rate'
    ,CONVERT(DECIMAL(15, 3), (CONVERT(DECIMAL(15, 3), sellamount) / CONVERT(DECIMAL(15, 3), rateactual))) AS 'local amount'
FROM currencyfxconversions

Upvotes: 6

Ali.azimi
Ali.azimi

Reputation: 103

Your issue is that you're using a column that you use in the same context. You can't use am alias as a table column in the select context. You must repeat the last alias queries, as follows:

SELECT referenceid
,CONVERT(DECIMAL(15, 3), sellamount) AS 'amount'
,CONVERT(DECIMAL(15, 3), rateactual) AS 'Rate'
,CONVERT(DECIMAL(15, 3), (CONVERT(DECIMAL(15, 3), sellamount) / convert(DECIMAL(15, 3), rateactual))) AS 'local amount'

Upvotes: 1

Mayur Sawant
Mayur Sawant

Reputation: 11

In the original query,

convert(decimal(15,3),'amount' / 'rate')

Must be replaced with:

CONVERT(CONVERT(decimal(15,3), sellamount) / CONVERT(decimal(15,3), rateactual))

Upvotes: 0

Related Questions