• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 221
  • 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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

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