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

dbaSQL
dbaSQL used Ask the Experts™
on
There is a flag in our ClosingAccounts table called 'Holder' -- This is just an identifier of sorts, helps us to route activity, as needed.  The objective is to have one 'Holder' for each closing account group.

At start of this project, there was only one Holder for all accounts, so I was including it in the retrieval like this:


DECLARE @Holder INT
SET @Holder = (SELECT 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

That was fine, it just returned the same @Holder for every record returned in the dataset.  Now, we've got multiple Holders, and my collection is failing with this:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The problem is that I still want to return that default Holder if a group does not have a Holder defined.  

Basically, I need to return the Holder for every record in this dataset.  If a holder is defined, great, that is what is returned.  Otherwise, I want to return the default one that I was collecting before.

The default holder is easily identifiable -- it has a Team of 'Holder', or I could just go in for the TAcctID = 175.  

I believe I could case the output of the Holder in that join, but I am looking for a little help with that CASE.



Please advise.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
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

Open 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 

Open in new window

Author

Commented:
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?
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
hmmm...
possibly going into some conditional statement:

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

SELECT
                   am.OAcctID,
         am.TAcctID,
         case when ca.holder = 1 then ca.TAcctID else @Holder end 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,
case when ca.holder = 1 then ca.TAcctID else @Holder end Holder

Open in new window

Author

Commented:
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?

Author

Commented:
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

Author

Commented:
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

Author

Commented:
did you see my last post, angeliii?  what do you think?
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

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

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial