Post reply

NULL(DT_I4) usage in a Derived Column

  • February 11, 2015 at 10:45 am

    #304194

    Here is what I have...

    Two OLE DB Source task that go directly into Sort tasks which end up going thru a merge join task for a FULL OUTER JOIN. The result of a FULL OUTER JOIN is that some of the data could come back as NULL.

    I would like to use a Derived Column task to add Column A (which comes in as 1) to Column B (which comes in as NULL due to the FOJ), but that cant happen, due to the NULL.

    So I need a way to tell Column B, if you are NULL, please replace your self with a 0. Then I would be adding 1 + 0 to get 1.

    ISNULL is a Boolean, so I am trying to figure out NULL(DT_I4) as my data coming in is of that data type.

    If any one could point me in the right direction on this, I would greatly appreciate it.

  • February 11, 2015 at 10:48 am

    #1777020

    I give up so easily and ask for help!!! I believe I have this figured out.

    (ISNULL(ColumnA) ? 0 : ColumnA) + (ISNULL(ColumnB) ? 0 : ColumnB)

    Thanks

  • February 11, 2015 at 10:59 am

    #1777023

    It's a shame that this functionality got implemented until 2012 using REPLACENULL().

    https://msdn.microsoft.com/en-us/library/hh479601(v=sql.110).aspx

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

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

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