SQL Error: Multiple columns are specified in an aggregated expression containing an outer reference-CodePudding

I am receiving this error when trying to execute the query below. Any ideas or suggestions?

SELECT * 
FROM TB_Transactions ft
WHERE (SELECT SUM(IIF(ft2.TransferFromId = ft.TransferFromId, -ft2.Amount, ft2.Amount))
       FROM TB_Transactions ft2
       WHERE (ft2.TransferFromId = ft.TransferFromId
              OR ft2.TransferToId = ft.TransferToId)
         AND ft2.Created <= ft.Created) = 0;

I do not understand how to remake the request so that the error is gone.

Target: display all fields from the TB_Transactions for those transactions after which the sender's(TransferFromId) account had 0

Expected result:

14| 21| 10.00000000| 1|2022-07-07 08:05:40.200

Because the first transaction (transfer 10) for account 14: 0-10=-10
Second transaction (getting 20) for account 14: -10 20=10
Third transaction (transfer 10) for account 14: 10 -10=0
Fourth transaction (transfer 10) for account 14: 0 -10=-10

If only I knew how to write the correct query. For account 14, a subquery that adds up to 0 maybe that will make it clearer.

transaction list for account 14

Create table:

CREATE TABLE [dbo].[TB_Transactions]
(
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [TransferFromId] [bigint] NULL,
    [TransferToId] [bigint] NULL,
    [Amount] [decimal](21, 8) NOT NULL, 
    [Created] [datetime2](7) NOT NULL,

    CONSTRAINT [PK_FG_Transactions_Id] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
                WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
                      OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Sample data:

INSERT INTO TB_Transactions (TransferFromId, TransferToId, Amount, Created)
VALUES
     (17,18,5.00000000,'2022-06-22 16:19:19.8960000'),
     (21,17,12.00000000,'2022-06-01 02:04:27.3970000'),
     (14,19,10.00000000,'2022-05-03 15:43:47.0680000'),
     (15,20,23.00000000,'2022-06-07 11:34:32.1580000'),
     (12,21,100.00000000,'2022-05-05 02:54:57.7000000'),
     (15,14,20.00000000,'2022-06-24 12:57:41.0670000'),
     (19,20,43.00000000,'2022-06-10 00:46:35.3150000'),
     (21,17,54.00000000,'2022-05-05 00:18:24.6590000'),
     (15,18,28.00000000,'2022-05-02 22:06:25.3310000'),
     (13,15,62.00000000,'2022-05-06 00:05:51.2740000'),
     (15,NULL,2.00000000,'2022-06-01 01:05:51.2710000'),
     (NULL,17,3.00000000,'2022-06-02 02:05:51.2770000'),
     (19,NULL,20.00000000,'2022-07-02 02:05:45.2000000'),
     (15,19,50.00000000,'2022-07-08 07:05:40.2000000'),
     (14,21,10.00000000,'2022-07-07 08:05:40.2000000'),
     (14,21,10.00000000,'2022-07-08 09:05:40.2010000');

CodePudding user response：

You ne4ed an outer SELECT to sum the amount, a querk from sql server, but your sampe data has no such result sets

SELECT *  FROM TB_Transactions ft
 WHERE  (SELECT SUM(myamount)
          FROM
              (SELECT iif(ft2.TransferFromId=ft.TransferFromId,-ft2.Amount,ft2.Amount) myamount
       from TB_Transactions ft2
       WHERE (ft2.TransferFromId =ft.TransferFromId
          or  ft2.TransferToId = ft.TransferFromId)
         and ft2.Created <= ft.Created) t1 )   = 0

Id | TransferFromId | TransferToId |      Amount | Created                    
-: | -------------: | -----------: | ----------: | :--------------------------
15 |             14 |           21 | 10.00000000 | 2022-07-07 08:05:40.2000000

SELECT *,(SELECT SUM(myamount)
          FROM
              (SELECT iif(ft2.TransferFromId=ft.TransferFromId,-ft2.Amount,ft2.Amount) myamount
       from TB_Transactions ft2
       WHERE (ft2.TransferFromId =ft.TransferFromId
          or  ft2.TransferToId = ft.TransferFromId)
         and ft2.Created <= ft.Created) t1 ) test   FROM TB_Transactions ft

Id | TransferFromId | TransferToId |       Amount | Created                     |          test
-: | -------------: | -----------: | -----------: | :-------------------------- | ------------:
 1 |             17 |           18 |   5.00000000 | 2022-06-22 16:19:19.8960000 |   64.00000000
 2 |             21 |           17 |  12.00000000 | 2022-06-01 02:04:27.3970000 |   34.00000000
 3 |             14 |           19 |  10.00000000 | 2022-05-03 15:43:47.0680000 |  -10.00000000
 4 |             15 |           20 |  23.00000000 | 2022-06-07 11:34:32.1580000 |    9.00000000
 5 |             12 |           21 | 100.00000000 | 2022-05-05 02:54:57.7000000 | -100.00000000
 6 |             15 |           14 |  20.00000000 | 2022-06-24 12:57:41.0670000 |  -11.00000000
 7 |             19 |           20 |  43.00000000 | 2022-06-10 00:46:35.3150000 |  -33.00000000
 8 |             21 |           17 |  54.00000000 | 2022-05-05 00:18:24.6590000 |  -54.00000000
 9 |             15 |           18 |  28.00000000 | 2022-05-02 22:06:25.3310000 |  -28.00000000
10 |             13 |           15 |  62.00000000 | 2022-05-06 00:05:51.2740000 |  -62.00000000
11 |             15 |         null |   2.00000000 | 2022-06-01 01:05:51.2710000 |   32.00000000
12 |           null |           17 |   3.00000000 | 2022-06-02 02:05:51.2770000 |          null
13 |             19 |         null |  20.00000000 | 2022-07-02 02:05:45.2000000 |  -53.00000000
14 |             15 |           19 |  50.00000000 | 2022-07-08 07:05:40.2000000 |  -61.00000000
15 |             14 |           21 |  10.00000000 | 2022-07-07 08:05:40.2000000 |    0.00000000
16 |             14 |           21 |  10.00000000 | 2022-07-08 09:05:40.2010000 |  -10.00000000

*db<>fiddle here*strong text

CodePudding user response：

SQL Server has weird rules surrounding usage of outer references (such as ft.TransferFromId) in aggregations (such as SUM) inside correlated subqueries. You cannot use such a reference unless it is outside the aggregation.

A workaround is to CROSS APPLY the outer reference inside the subquery, so that it appears as part of the subquery scope.

SELECT *
FROM TB_Transactions ft
WHERE (SELECT SUM(IIF(ft2.TransferFromId = v.TransferFromId, -ft2.Amount, ft2.Amount))
       from TB_Transactions ft2
       CROSS APPLY (VALUES( ft.TransferFromId )) v(TransferFromId)
       WHERE (ft2.TransferFromId = ft.TransferFromId
          or  ft2.TransferToId = ft.TransferToId)
         and ft2.Created <= ft.Created
      ) = 0;

Alternatively, you can apply the IIF value

SELECT *
FROM TB_Transactions ft
WHERE (SELECT SUM(v.value)
       from TB_Transactions ft2
       CROSS APPLY (VALUES( IIF(ft2.TransferFromId = ft.TransferFromId, -ft2.Amount, ft2.Amount) )) v(value)
       WHERE (ft2.TransferFromId = ft.TransferFromId
          or  ft2.TransferToId = ft.TransferToId)
         and ft2.Created <= ft.Created
      ) = 0;

The phrasing of the error message is unfortunate, as "Multiple columns" is not necessary to trigger the error.