ISNULL clause with a condition

Question

ISNULL clause with a condition

YariLei

SSC Enthusiast

Points: 112
More actions
June 15, 2020 at 12:11 pm

#3763078

Hi,
I've got the following code to select the period where I have active data:
{ FIXED : MAX(IF NOT (ISNULL([Qty in tn])) THEN [Period] END)}
The problem I have is that the Qty in tn is only null when another column [Data Type] = ACT. How do I modify the above code to take into account the second column?
Cheers!
Regards,
Yari

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

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

Steve Jones - SSC Editor SSC Guru Points: 736173 More actions · Answer 1

Use an AND, is what I think. I'm not quite sure how you're doing this, but when you evaluate the ISNULL(), I assume you can access both the value of Qty and the value of [Data Type]. In that case, you can see ISNULL and [Data Type] = 'Act'.

However, are you sure that there cannot be a null here if there is a different value for [data type]? Or that you want different behavior? It's not quite clear what you mean here.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 2

"It Depends". If this is being used in the SELECT list of a query, you can do pretty much as you please and you'll have no problems. If it's a part of the criteria expressed in a WHERE or ON clause, you have to be REALLY care to not make the query Non_SARGable.

From your original post, I'd guess you code snippet is from an item in the SELECT list but we can't know for sure from what you posted. It would be a whole lot more helpful if you'd post the rest of the query.

Yep... I know that you can't normally include a MAX() aggregation in the WHERE or ON clauses but it's amazing what people have done to do just that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 3

Jeff Moden wrote:

If this is being used in the SELECT list of a query,

Jeff, if that is part of a SELECT list, what SQL dialect would that be? I don't know what the above is, but T-SQL it is not. Could it be something for SS[AR]S?

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 4

Erland Sommarskog wrote:

Jeff Moden wrote:
If this is being used in the SELECT list of a query,
Jeff, if that is part of a SELECT list, what SQL dialect would that be? I don't know what the above is, but T-SQL it is not. Could it be something for SS[AR]S?

Lordy,,, I totally ignored the "(FIXED:" notation because I thought it was a copy'n'paste error, possibly from some spec. Thanks for pointing that out, Erland.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)