ISNULL clause with a condition

  • YariLei

    Valued Member

    Points: 62

    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

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720887

    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: 997103

    "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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Erland Sommarskog

    SSC-Insane

    Points: 23929

    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: 997103

    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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

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

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