Post reply

ISNULL - Replacement Value is Null, Look Up Second Replacment Value

  • October 19, 2011 at 7:03 pm

    #245625

    Hello,

    I have been using ISNULL to return a replacement value.

    Now I want to step it up a level and say if the 1st replacement value is also null, that I want it to look up a second replacement value.

    Is this possible and how would I write the statement? I have tried a few different options (as below) but can't seem to crack it.

    ISNULL((DHAssays.Fe, DHQCAssays.Fe)(DHQCAssays.Fe, DHSTDAssays.Fe))

    Any help is much appreciated.

    Many thanks, Marisa.

  • October 19, 2011 at 8:29 pm

    #1397041

    the coalesce statement is what you are after; it accepts a param array of values(meaning no limit on the number of values to test), and returns the first non-null value

    SELECT COALESCE(DHAssays.Fe, DHQCAssays.Fe, DHSTDAssays.Fe,42)

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 19, 2011 at 8:51 pm

    #1397042

    Thanks Lowell,

    The coalesce statement works and is nice and simple.

    It looks for a value in the columns I specify (in order) and can return a default (ie: 42 in your example) if no values are found.

    Just what I'm after, brilliant!

    Cheers, Marisa

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

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