Nulls and Compare Strings

[New2DS]

Hi All,

We have 50 columns in our source and we don't want to allow nulls in any of the columns. Which will be the the easiest way to do it. Writing a constrait using Not(Isnull(InputColumn)) on every column will be a big pain I think.

One more simple question for you guys but not for me, how do we compare 2 strings without casesensitive. I mean A##rr and A##RR should show as equal. My requirement do not allow me change one of the columns case that is I cannot use Upcase or DownCase to change the case and compare.

thanks,

[chulett]

One more simple question for you guys but not for me, how do we compare 2 strings without casesensitive. I mean A##rr and A##RR should show as equal. My requirement do not allow me change one of the columns case that is I cannot use Upcase or DownCase to change the case and compare.

Sure you can! Simply use UpCase in your test. No need to change the actual values.

[New2DS]

Thanks Craig,

Do you think is there any easy way to handle all the Nulls??

[roy]

Hi,
regarding your first question
you can build a transformation to check it so you'll have fewer characters to write.

Code: Select all

If IsNULL(%arg%) Then <substitute string> Else %arg%

now since char columns and numeric columns tend to have different default values you'll need 2 seperate ones.
I guess having Default constraints in your DB is out of the question (and slows down the load performance)
there are other ways but none exists that does the job for you automatically.

IHTH,

[chucksmith]

For your constraint, you may consider taking advantage of some of the other properties of null. If you concatenate all of your columns together, and any one of them is null, then the result is null. Now you are left with a single call to IsNull().

Other tricks like this may work. In any case, implement it in stage variables. It is easier to see later.