Subquery returned more than 1 value. This is not permitted...

please try:

DECLARE @Holder INT
SELECT @Holder = TAcctID FROM dbo.ClosingAccounts WHERE Holder = 1 AND Active = 1

SELECT
                   am.OAcctID,
         am.TAcctID,
         @Holder
      FROM
        dbo.AMapping am WITH (NOLOCK) INNER JOIN dbo.EOAccts a WITH (NOLOCK)
          ON am.OAcctID = a.OAcctID
                  INNER JOIN dbo.ClosingAccts ca WITH (NOLOCK)
          ON am.TAcctID = ca.TAcctID
      WHERE
        am.OAcctID = COALESCE(@OAcctID,am.OAcctID)
      GROUP BY
        am.OAcctID,
        am.TAcctID,
        @Holder

Select allOpen in new window

but the error message tells you that the query:
SELECT TAcctID FROM dbo.ClosingAccounts WHERE Holder = 1 AND Active = 1
returns more the 1 rows. it might be the same value for TAcctID for all the rows, but still more than 1 row.

so, this makes me think you want:

SELECT
                   am.OAcctID,
         am.TAcctID,
         ca.TAcctID 
      FROM
        dbo.AMapping am WITH (NOLOCK) INNER JOIN dbo.EOAccts a WITH (NOLOCK)
          ON am.OAcctID = a.OAcctID
                  INNER JOIN dbo.ClosingAccts ca WITH (NOLOCK)
          ON am.TAcctID = ca.TAcctID
      WHERE
        am.OAcctID = COALESCE(@OAcctID,am.OAcctID)
      GROUP BY
        am.OAcctID,
        am.TAcctID,
        ca.TAcctID 

Select allOpen in new window

Hi angeliii.  Thank you very much.

Yes, there is more than one record in the tables with Holder = 1.  Right now, 99% have a Holder = 0, there is one record for TAcctID = 111 where Holder = 1, and there is one record for TAcctID = 175, where Holder = 1.  Both of these non-0 records reside in different groups.  All other members of those same groups would inherit TAcctID 111 or 175 respectively, as their Holder.

This Holder is defined in the ClosingAccounts ca table.  
The AMapping table is a mapping between EoAccts and ClosingAccounts, and AMapping/EOAccts are populated only if the TAcctID actually has placed orders.  See here:

AMapping table:            AMappingID, OAcctID,TAcctID
EOAccts table:               OAcctID,Acct,User,Pwd,ExchUserName,ExchPwd,Active
ClosingAccounts table:  TAcctID,UserID,Team,Strategy,Group,Holder,Active


My DEFAULT closing account holder (aka, 'holder') will never place orders.  He is just the watcher, where orders are placed into his default account if/when another holder has not been defined for that group.  It just helps them keep an eye on the activity.  The group, as you see in the above definition, is also defined in the ca table, right next to 'holder'.

Your first suggestion won't work because yes, right now there are two non-0 TAcctID's in the closing accounts table, and all of the rest are zero's.  

The second suggestion would work, except that I want to return the 175 TAcctID for all members of TAcctID 175's group, because his holder flag = 1.  
I want to return the TAcctID 111 for all members of the TAcctID 111's group, because his holder flag = 1.
And... I want to return 175 TAcctID for all members of other groups where the holder is not defined, or = 0.  (my DEFAULT closing account, or the 'holder')


I really hope I've explained that clearly.


The output of your second suggestion returns five records where Holder = 111.  This is correct, as there are five values in AMapping where TAcctID = ClosingAccts.TAcctID, and five values in EOAccts with the corresponding OAcctID's.  It returns nothing for 175, again, because this guy doesn't trade (and has no records in the other two tables in the JOIN), and the Holder = 0 for everything else returned, because it is not yet defined in each of their corresponding groups.

I somehow need to CASE the reference to my default holder TAcctID when another value for that group has not been defined.


What do you think, angeliii?

almost, angeliii.  

select * from closingaccounts where TAcctID = 111 and holder = 1          
   returns 1 record, group = 184
select * from closingaccounts where group = 184
   returns 6 records, one of which is holder, all are active
select * from amapping where TAcctID IN (the list output from the previous query)
   returns 26 records
select am.*,ca.holder from dbo.amapping am join dbo.closingaccounts ca
 on am.TCAcctID = ca.TCAcctID
where ca.group = 184
   returns 26 records BUT, the holder for the first five records is returned as 1, the rest come back as 0

Your last suggestion is doing the same.  

Because the TCAcctID 111 is the holder for group 184, all records returned for that group should have the that TCAcctID returned as the holder.


What do you think?

case when ca.holder = 1 then ca.TAcctID else @Holder end Holder

the problem here is that it's going in for anything where holder = 1.
i need to divvy it up just a little further, by group

maybe?

It returns all 26 referenced in this post 37852022 as Holder = 111.  That is correct.
I don't really need the Group in the final output, but it helps to debug the construct.

what do you think, angeliii?

DECLARE @Holders TABLE (GroupID INT,TAcctID INT)
INSERT @Holders (GroupID,TAcctID)
SELECT GroupID,TAcctID
FROM dbo.ClosingAccounts
WHERE Holder = 1
GROUP BY GroupID,TAcctID


      SELECT
         am.OAcctID,
         am.TAcctID,
         ca.GroupID,
         Holder = CASE
                  WHEN ca.GroupID = b.GroupID THEN b.TAcctID
                  WHEN ca.GroupID <> b.GroupID THEN 175 END
    FROM
             dbo.AMapping am INNER JOIN dbo.EOAccts eoa
          ON am.OAcctID = eoa.OAcctID INNER JOIN dbo.ClosingAccounts ca
            ON am.TAcctID = ca.TAcctID JOIN @Buckets b
              ON ca.GroupID = b.GroupID
    WHERE
            ca.GroupID = 184
      ORDER BY
            ca.GroupID

did you see my last post, angeliii?  what do you think?

if  it (the output) looks correct to you, I don't see what I could improve :)

Your suggestion got me almost there, I finished the rest of it.  Thank you, angeliii.  
Much appreciated.