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)
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
am.OAcctID = COALESCE(@OAcctID,am.OAcctID)
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.