IF Column THEN Value AS Name.

Question

IF Column THEN Value AS Name.

pedroccamara

SSC-Addicted

Points: 431
More actions
October 10, 2022 at 9:53 am

#4097390
Hello everyone
First, I want you all to know that I always search for an answer before posting here. I really don't like to bother other people with my problems. That's what happen when I'm learning SQL. But one thing is for sure: I'm saving all those queries for later to use.
This time, I'm trying to make 3 columns after a select statement and right before the FROM clause. I want to check first, if the column is equal to 'D' then add the M.Valor and name it as Debits else, another column as Credit. At the end, I want another column, Net, to be the difference between debits and credits.
Here is my code:
```
 (CASE
WHEN M.Natureza = 'D'
THEN M.Valor
ELSE 0
END) AS Debito,
(CASE
WHEN M.Natureza = 'C'
THEN M.Valor
ELSE 0
END) AS Credito
```
I hope you can help me because it's missing the value Net (Debits-Credits) and I feel this could be done differently.
Thanks in advance

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16154 More actions · Answer 1

use the same outer/cross apply construct you were given on your other thread

that is one of the good cases for it - define the values to use on the apply part and then use it on the main select to both output values and create new columns by manipulating the calculate values

pedroccamara SSC-Addicted Points: 431 More actions · Answer 2

Hi again Frederico

I guess for you it is very simple but it's not for me, as a newbie I am.

I've tried again and don't know how. Could you help?

Let me tell you what I would like to have:

IF M.Natureza = 'D' then M.Valor AS Debit

ELSE M.Valor AS Credit

Net = (Debit - Credit)

Thanks a lot Frederico

frederico_fonseca SSCoach Points: 16154 More actions · Answer 3

as I've shown you on the other thread you have a construct that calculates the values you need.

you then use those calculated values on the main query.

pseudo code

select ...
     , calc.debit
     , calc.credit
     , calc.debit - calc.credit as Net
from xxx
cross apply (select case statement as Debit
                  , case statement as Credit
            ) calc

drew.allen SSC Guru Points: 76985 More actions · Answer 4

All expressions in a SELECT clause are evaluated at the same time. This means that you cannot use an alias in the same SELECT statement in which it is defined--which is what you are trying to do. This is why people are suggesting CROSS APPLY. It separates the alias definition and the alias use.

Also, your Net field may not have the value that you are expecting, because it is based solely on one row. It is either going to be the same as the Credit or negative Debit.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA