CODEWITHSUNDEEP
sql-serverssrs-2012
JoAnne
JoAnne

Reputation: 3

How to properly use SQL CASE statement

How do I use a CASE statement to apply to all values corresponding to a specific field in a column?

For example, I want all payments in every column to appear as negatives but this only applies to the first column. Also, I want it to apply to all payments in every column.

 CASE WHEN ardoc.DocType = 'PA' 
      THEN arDoc.DocBal*(-1) ELSE ardoc.DocBal 
 END AS [Document Balance]

Note: 'PA' stands for payments

Upvotes: 0

Views: 68

Answers (3)

Alan Schofield
Alan Schofield

Reputation: 21683

As Tab Allerman said, you can't do this with a single CASE statement but there might be another approach.

Create a table (temporary or permanent) that contains the DocType plus a multiplier, then join to this.

Something like

DECLARE @t TABLE(DocType varchar(2), multiplier int)
INSERT INTO @t VALUES
('PA', -1),
('XX', 1),
('YY', 1)

Then you query would look something like

SELECT 
    arDoc.DocBal * ISNULL(t.Multiplier, 1) as [Document Balance],
    myNextColumn * ISNULL(t.Multiplier, 1) as [My Next Output Column]
From MyTable a
    LEFT JOIN @t t on a.DocType = t.DocType

I've done a left join here and used ISNULL so that if no entry is found in @t then it assumes a multiplier of 1.

You still have to do each column but the code smaller and if you need new multipliers, you don't have to change the case statements.

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453057

You could avoid repeating the expression in this case with something like

SELECT DocBal = multiplier * DocBal,
       Col2 = multiplier * Col2,
       Col3 = multiplier * Col3
FROM   arDoc
       CROSS APPLY (VALUES(IIF(DocType = 'PA', -1, 1))) V(multiplier);

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31775

You cannot write a single CASE expression that will return more than one column. You will need to write a separate CASE for each column.

Upvotes: 2

Related Questions