I'm having trouble getting a CASE statement to work in a nested select. I think I'm close but I can't quite get the syntax right. So far I've tried:
SELECT l.*,
Credit = (
CASE WHEN ISNULL(M.POSTCODE,'') <> '' THEN
sum(Credit) from Balance cb Where LEFT(LTrim(cb.AccountHolder),4) LIKE LEFT(LTrim(m.Company),4)
ELSE
sum(Credit) from Balance cb Where LEFT(LTrim(cb.AccountHolder),4) LIKE LEFT(LTrim(m.Company),4)
END
)
FROM live l INNER JOIN master m on m.ClientID = L.ClientID
WHERE ClientID = 12345
And also:
SELECT l.*,
(SELECT
CASE WHEN ISNULL(M.POSTCODE,'') <> '' THEN
sum(Credit) from Balance cb Where LEFT(LTrim(cb.AccountHolder),4) LIKE LEFT(LTrim(m.Company),4)
ELSE
sum(Credit) from Balance cb Where LEFT(LTrim(cb.AccountHolder),4) LIKE LEFT(LTrim(m.Company),4)
END
) AS Credit
FROM live l INNER JOIN master m on m.ClientID = L.ClientID
WHERE ClientID = 12345
Which seems to match the MSDN examples at http://msdn.microsoft.com/en-us/library/ms181765.aspx . Am I missing something?
I think you need to add some selects before your sum subqueries.
SELECT l.*,
Credit = (
CASE WHEN ISNULL(M.POSTCODE,'') <> '' THEN
(SELECT sum(Credit) from Balance cb Where LEFT(LTrim(cb.AccountHolder),4) LIKE LEFT(LTrim(m.Company),4))
ELSE
(SELECT sum(Credit) from Balance cb Where LEFT(LTrim(cb.AccountHolder),4) LIKE LEFT(LTrim(m.Company),4))
END
)
FROM live l
INNER JOIN master m on m.ClientID = L.ClientID
WHERE ClientID = 12345
Your select's are also exactly the same, so there isn't really a need for a case unless of course you intend for them to be different.