• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 228
  • Last Modified:

How to fix this query?

ALTER FUNCTION [dbo].[Func_Bank_Balance]
(
@accountid int,
@addoneday datetime
)
Returns decimal(18,4)
AS
BEGIN
Declare @return decimal(18,4)
Declare @return1 decimal(18,4)
Declare @bal int

select @bal=a.balance from account a where a.id=@accountid
if @bal is null
set @bal = 0

select @return1 = sum(amount + countervalue) from alltransaction
where accountid=@accountid and trandatetime < @addoneday and accountid > 0  
if @return1 is null
set @return1 = 0

set @return = @bal + @return1
return @return
END


The result of
select a.balance from account a where a.id='12148'
2.82

The result of
select sum(amount + countervalue) from alltransaction where accountid='12148' and trandatetime < '2009-12-06 06:00:00' and accountid > 0
null

The result of
Select dbo.Func_Bank_Balance('12148', '2009-12-06 06:00:00') accountbal from alltransaction t where t.trandatetime < '2009-12-06 06:00:00'
2.0000

It is wrong, it should be 2.8200

How should I fix it?




0
techques
Asked:
techques
2 Solutions
 
DaveSamCommented:
Shouldn't declare @bal int be declare @bal decimal(18,4)?
0
 
leakim971PluritechnicianCommented:
Hello techques,

Use ISNULL : http://msdn.microsoft.com/en-us/library/aa933210(SQL.80).aspx

Regards.

ALTER FUNCTION [dbo].[Func_Bank_Balance] 
(
@accountid int,
@addoneday datetime
)
Returns decimal(18,4)
AS
BEGIN
Declare @return decimal(18,4)
Declare @return1 decimal(18,4)
Declare @bal int

select @bal=a.balance from account a where a.id=@accountid
if @bal is null
set @bal = 0

select @return1 = sum(ISNULL(amount,0) + ISNULL(countervalue,0)) from alltransaction 
where accountid=@accountid and trandatetime < @addoneday and accountid > 0  
if @return1 is null
set @return1 = 0

set @return = @bal + @return1
return @return
END

Open in new window

0
 
tigin44Commented:
try this

Select dbo.Func_Bank_Balance(a.balance) accountbal
from alltransaction t
where t.trandatetime < '2009-12-06 06:00:00'
0
 
techquesAuthor Commented:
@bal decimal(18,4) yes, it has fixed
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now