locked
caught error on query RRS feed

  • Question

  • User29410129 posted
    ALTER procedure [dbo].[SPAccountAddNewAmount]
    (
    @Amount numeric,
    @Date date,
    @UserName nvarchar(50),
    @TransactionTypeName nvarchar(100)
    )
    As
    Begin
    declare @UserID bigint
    declare @TransactionTypeID bigint
    Declare @PreviousAmount decimal(18,0)
    
    
    select @TransactionTypeID = TransactionTypeID from TransactionType where TransactionTypeName = @TransactionTypeName
    select @UserID = UserID from [User] where UserName = @UserName
    Insert into TransactionHistory(UserID, Amount, TransactionDate, TransactionTypeID)
    values (@UserID, @Amount, @Date,@TransactionTypeID)
    
    select @PreviousAmount = CurrentBalance from Account
    
    if @TransactionTypeID = 1
    Begin
    update Account set CurrentBalance = SUM( @PreviousAmount + @Amount) where AccountID = 1
    End
    
    Else if @TransactionTypeID = 2
    Begin
    update Account set CurrentBalance = ( @PreviousAmount - @Amount) where AccountID = 1
    End
    
    Else if @TransactionTypeID = 3
    Begin
    update Account set CurrentBalance = SUM( @PreviousAmount - @Amount) where AccountID = 1
    End
    
    Else
    Begin
    update Account set CurrentBalance = SUM( @PreviousAmount + @Amount) where AccountID = 1
    End 
    End

    i am trying to write this procedure i caught error on update query. "An aggregate may not appear in the set list of an UPDATE statement." Please tell me what's wrong with this query?

    Monday, October 3, 2016 6:31 PM

Answers

  • User77042963 posted

    Try this (by the way, you need to enforce relationship between these tables. Without your table DDLs, here is a guess).

    ALTER procedure [dbo].[SPAccountAddNewAmount]
    (
    @Amount numeric,
    @Date date,
    @UserName nvarchar(50),
    @TransactionTypeName nvarchar(100)
    )
    As
    Begin
    declare @UserID bigint
    declare @TransactionTypeID bigint
    Declare @PreviousAmount decimal(18,0)
    
    
    select @TransactionTypeID = TransactionTypeID from TransactionType where TransactionTypeName = @TransactionTypeName
    select @UserID = UserID from [User] where UserName = @UserName
    Insert into TransactionHistory(UserID, Amount, TransactionDate, TransactionTypeID)
    values (@UserID, @Amount, @Date,@TransactionTypeID)
    
    ;with mycte as (
    Select SUM(CurrentBalance + @Amount* (Case when @TransactionTypeID in (2,3) then -1 else 1 End) ) CurrentBalanceNew
    from Account
    where AccountID = 1
    Group by AccountID
    )
    
    Update mycte
    Set CurrentBalance=CurrentBalanceNew;
    
    
     End

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 3, 2016 6:48 PM

All replies

  • User77042963 posted

    Try this (by the way, you need to enforce relationship between these tables. Without your table DDLs, here is a guess).

    ALTER procedure [dbo].[SPAccountAddNewAmount]
    (
    @Amount numeric,
    @Date date,
    @UserName nvarchar(50),
    @TransactionTypeName nvarchar(100)
    )
    As
    Begin
    declare @UserID bigint
    declare @TransactionTypeID bigint
    Declare @PreviousAmount decimal(18,0)
    
    
    select @TransactionTypeID = TransactionTypeID from TransactionType where TransactionTypeName = @TransactionTypeName
    select @UserID = UserID from [User] where UserName = @UserName
    Insert into TransactionHistory(UserID, Amount, TransactionDate, TransactionTypeID)
    values (@UserID, @Amount, @Date,@TransactionTypeID)
    
    ;with mycte as (
    Select SUM(CurrentBalance + @Amount* (Case when @TransactionTypeID in (2,3) then -1 else 1 End) ) CurrentBalanceNew
    from Account
    where AccountID = 1
    Group by AccountID
    )
    
    Update mycte
    Set CurrentBalance=CurrentBalanceNew;
    
    
     End

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 3, 2016 6:48 PM
  • User-1672470423 posted

    Remove the SUM function, its not required as you are already using "+" to sum value and "-" to subtract.

    It will solve your problem.

    Monday, October 3, 2016 6:56 PM