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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
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