Data Type from Calculation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Data Type from Calculation

Author

Topic

Les.61
Starting Member

49 Posts

Posted - 2009-11-22 : 22:13:36

I am creating a view and have an interest rate which has data type (numeric(10,3),null).
I then created a calculated field as follows:
'1plusInt' = (1 + t.interestrate/100000)
This has a data type of (numeric(18,10),null)

I have also created a calculated field as follows
'1power' = power('1plusInt',12)
This has a data tpe of (float,null)

If I try to open the view I get the error message

"error converting data type varchar to float."

Can I force the 1power calculated field to be numeric?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-22 : 22:18:50

'1power' = power('1plusInt',12)

'1power' = power(1plusInt,12)

Les.61
Starting Member

49 Posts

Posted - 2009-11-22 : 22:31:39

'1plusInt' = (1 + t.interestrate/100000),
'1power' = power(1plusInt,12)

now returns "Incorrect syntax near 'plusInt'."

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-22 : 22:40:26

How 'bout showing the actual query.

Les.61
Starting Member

49 Posts

Posted - 2009-11-22 : 22:44:10

use reporting
go
alter view V726
as
select
a.id, a.account, a.accountname, t.balance
, t.repaymentamount, t.repaymentfrequency, a.remainingrepaymentterm
, t.interestrate,
'1plusInt' = (1 + t.interestratestandard/100000),
'1power' = power(1plusInt,12),

Frequency = case
when t.repaymentfrequency = 'M' then 12
when t.repaymentfrequency = 'F' then 26
when t.repaymentfrequency = 'W' then 52
end
from dbo.Transactions as t
inner join dbo.Account as a
on t.id = a.id
where a.Account like 'l%' and t.balance <> 0.00

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-22 : 22:50:21

[code]alter view V726
as
select a.id, a.account, a.accountname, t.balance
, t.repaymentamount, t.repaymentfrequency, a.remainingrepaymentterm
, t.interestrate,
'1plusInt' = (1 + t.interestratestandard/100000),
'1power' = power((1 + t.interestratestandard/100000),12),
Frequency = case
when t.repaymentfrequency = 'M' then 12
when t.repaymentfrequency = 'F' then 26
when t.repaymentfrequency = 'W' then 52
end
from dbo.Transactions as t
inner join
dbo.Account as a
on t.id = a.id
where a.Account like 'l%'
and t.balance <> 0.00
[/code] this work?

Les.61
Starting Member

49 Posts

Posted - 2009-11-25 : 17:18:46

Yes this did help. Many thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-26 : 01:55:14

quote:
Originally posted by Les.61

use reporting
go
alter view V726
as
select
a.id, a.account, a.accountname, t.balance
, t.repaymentamount, t.repaymentfrequency, a.remainingrepaymentterm
, t.interestrate,
'1plusInt' = (1 + t.interestratestandard/100000),
'1power' = power(1plusInt,12),

Frequency = case
when t.repaymentfrequency = 'M' then 12
when t.repaymentfrequency = 'F' then 26
when t.repaymentfrequency = 'W' then 52
end
from dbo.Transactions as t
inner join dbo.Account as a
on t.id = a.id
where a.Account like 'l%' and t.balance <> 0.00

It is because you can't use alias name directly in the query without using a derived table (ORDER BY is an exception)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources