Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2002
    Posts
    2

    Unanswered: Interesting UPDATE STATEMENT for SQL

    This Following statement executes perfectly in SQL2000
    but not in SQL7.0 it gives the message
    Server: Msg 147, Level 16, State 2, Procedure spTEST1, Line 57
    An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.


    Create Procedure "spTEST1"
    As

    --Update Processed Flags
    UPDATE tblTransaction
    SET TransProcessed = 1
    FROM dbo.tblTrustGroupTrust
    INNER JOIN dbo.tblTrust ON dbo.tblTrustGroupTrust.TgtTrustID = dbo.tblTrust.TrustID
    INNER JOIN dbo.tblTransaction ON dbo.tblTrust.TrustID = dbo.tblTransaction.TransTrustID
    INNER JOIN dbo.tblShareholder ON dbo.tblTransaction.TransShID = dbo.tblShareholder.ShID
    INNER JOIN dbo.tblTransType ON dbo.tblTransType.TransTypeID = dbo.tblTransaction.TransTypeID
    WHERE (dbo.tblTrustGroupTrust.TgtTrustGroupID = 3) AND (dbo.tblTransaction.TransProcessed = 0)
    AND (dbo.tblShareholder.ShPaymentMethod = 1) AND (dbo.tblShareholder.ShDeceased = 0) AND tblShareholder.ShBankAccount IS NOT NULL
    AND EXISTS
    (
    SELECT dbo.tblTransaction.TransShID
    FROM dbo.tblTrustGroupTrust
    INNER JOIN dbo.tblTransaction ON dbo.tblTrustGroupTrust.tgtTrustID = dbo.tblTransaction.TransTrustID
    WHERE (dbo.tblTransaction.TransProcessed = 0) AND (dbo.tblTrustGroupTrust.TgtTrustGroupID = 3) AND (dbo.tblShareholder.SHID = dbo.tblTransaction.TransShID)
    GROUP BY dbo.tblTransaction.TransShID
    HAVING SUM(tblTransaction.TransAmt) >= 10
    )


    When using a straight select on this statement in SQL7.0 it works fine:
    SELECT tblTransaction.TransProcessed
    FROM ......

    Can anyone shed some light on this?

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    The aggregate is on the table being updated - although it is in a subquery this is correllated and so still fails the test.

    If you can change the having clause to
    HAVING SUM(dbo.tblTransaction.TransAmt) >= 10

    so that it is using the copy of the table in the subquery then it should work.

  3. #3
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    It might be clearer if you use an alias for the subquery table like
    tblTransaction t2.

  4. #4
    Join Date
    Jan 2002
    Posts
    2
    Thanks, I very nearly tried to add the missing dbo prefix's in but I presumed that would be too trivial and the error was more complicated than something like that.

    Obviously SQL2000 is a little more relaxed on this sort of thing, as it works without error.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •