calculation in select
In sql server 2000 I have the following query in a stored procedure:
select amount,
amount * VAT / 100 as vatsum,
amount + (amount * VAT / 100) as total
from table1
1. if the value of VAT is 0 then the value of total also become 0 although it is not 0 because it suppose to add the
amount. What is the problem here.
2. I wanted to write it like this
select amount,
amount * VAT / 100 as vatsum,
amount + vatsum as total
from table
but it is not accepted . what is the problem and how do I write it so that I write the calculation of vatsum only once?
Thankyou
Anat
select amount,
amount * VAT / 100 as vatsum,
amount + (amount * VAT / 100) as total
from table1
1. if the value of VAT is 0 then the value of total also become 0 although it is not 0 because it suppose to add the
amount. What is the problem here.
2. I wanted to write it like this
select amount,
amount * VAT / 100 as vatsum,
amount + vatsum as total
from table
but it is not accepted . what is the problem and how do I write it so that I write the calculation of vatsum only once?
Thankyou
Anat
Whether you write is as "amount + vatsum" or "amount + (amount...." It would still calculate vatsum twice b/c vatsum is just an alias to that column. SQL Server would need to calculate it twice.
you could re-write it to eliminate the multiple calculations. Try the below query and see if it returns faster results.
select amount,
amount * VAT / 100 as vatsum,
amount + (amount * VAT / 100) as total
from table1
where amount <> 0
UNION ALL select 0,
0,
0
from table1
WHERE amount = 0
you could re-write it to eliminate the multiple calculations. Try the below query and see if it returns faster results.
select amount,
amount * VAT / 100 as vatsum,
amount + (amount * VAT / 100) as total
from table1
where amount <> 0
UNION ALL select 0,
0,
0
from table1
WHERE amount = 0
if amount is indexed you may return faster results this way.
ASKER
nobody solved the problem. the problem is not the fast of the query but that the total return 0 althogh in consists of
the amount which is not 0 and the vatsum which is 0
Can you help?
Anat
the amount which is not 0 and the vatsum which is 0
Can you help?
Anat
ASKER
Ok sorry, I found it. it's my mistake .
actually the VAT was written like this:
ISNULL(VAT, 0) and I didn't write it in the second part.
Sorry
Thankyou all
Anat
actually the VAT was written like this:
ISNULL(VAT, 0) and I didn't write it in the second part.
Sorry
Thankyou all
Anat
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thankyou you are write as you can see in my previuos answer I didn't show the complete query and there was the
problem that I already solved.
Anat
problem that I already solved.
Anat
amount * (VAT / 100) as vatsum,
amount + (amount * (VAT / 100)) as total
from table1