September 4, 2008 at 2:07 pm
I want to do a if null then value type to derived column. I have a column in a table that I want to say if column is NOTnull, then replace with value x
Here is what I had so far
PGL_FM_FK != NULL(DT_I4) ? PGL_FM_FK : PGL_FU_FK != NULL(DT_I4) ? PGL_FU_FK...., ETC
so basically, I have 6 columns in a database that in our new database have all be consolidated into one column. In the old system, there is a FK in only 1 of the 6 columns for any give row, so there will NEVER be a row with a FK in more then 1 of the 6 FK Columns. Above is what I am trying to do with only 2 columns as example. I want to to look at column 1, if it is NULL, then skip to 2nd column, if it is NOT null, then use that FK. Hope this makes sense.
February 1, 2010 at 1:00 pm
Have you tried Lookup tables?
Once I figured out how to use lookups, I can't imagine needing to replace values in a derived column object any longer.
February 2, 2010 at 6:32 am
Can you use a T-SQL query for your source data? COALESCE would do the work for you very nicely.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 3, 2010 at 12:37 am
Hi,
Below is the code I think you are looking for. I also recommend coding in the affirmative rather than the negative.
(ISNULL(COL1_FK) ? (ISNULL(COL2_FK) ? COL3_FK : COL2_FK) : COL1_FK)
You will obviously continue to 6 columns.
Unfortunately I haven't had a chance to test it, though I based it from derived column code I used which is tested below:
(Suffix == "I" ? "1" : (Suffix == "II" ? "2" : (Suffix == "III" ? "3" : (Suffix == "IV" ? "4" : (Suffix == "V" ? "5" : (Suffix == "VI" ? "6" : (Suffix == "VII" ? "7" : (Suffix == "VIII" ? "8" : (Suffix == "IX" ? "9" : (Suffix == "X" ? "10" : Suffix))))))))))
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply