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...
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?
3 Answers
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
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))
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'
Maya is a hardware enthusiast who tests and reviews smart home devices, smartphones, wearables, and audio gear. She focuses on practical consumer value and build quality.
