4

I need to detect whether either of two values are null, and if not, whether they differ.

The condition I have in my split is 

(ISNULL(ModuleLevelId) && !ISNULL(LEV_CODE)) || 
(!ISNULL(ModuleLevelId) && ISNULL(LEV_CODE)) || (ISNULL(LEV_CODE) ? 0 - ModuleLevelId : (DT_I4)LEV_CODE) != ModuleLevelId

but I am told that my expression leads to null, i.e. a non-Boolean condition.

Can anyone help me write the condition that will work please?

Many thanks

Patrick

pwmusic
  • 4,739
  • 4
  • 23
  • 14
  • I have split this conditional and made the first test a null check for either column. A subsequent condition then performs the cast and the comparison and I still get the message 'Source: Module Check for changes [3192] Description: The expression "ModuleLevelId != (DT_I4)LEV_CODE" on "output "LevelId changed" (5356)" evaluated to NULL, but the "component "Check for changes" (3192)" requires a Boolean results. – pwmusic Sep 19 '12 at 16:47
  • OK what you try to do if both values are not null ? `LEV_CODE - ModuleLevelId` ? And what you need to return if one of the values is null ? – Justin Sep 19 '12 at 18:38
  • Hi @justin, and thanks for replying. Essentially I'm trying to detect whether there's been a change between two values and this will determine a number of subsequent updates. The problem is NULLs keep breaking the comparisons – pwmusic Sep 20 '12 at 13:14
  • Sorry but your condition is not correct:) after `?` (Then) you can do subtraction but after `:` you **can't** compare values. Brackets closed too incorrectly. – Justin Sep 20 '12 at 16:56
  • Hello again @Justin Thanks for your persevering with me but I think my Boolean if test is correct. Can you explain which part is wrong? (ISNULL(LEV_CODE) ? 0 - ModuleLevelId : (DT_I4)LEV_CODE) should evaluate correctly and give me either "0 - ModuleLevelId" or "LEV_CODE casted to an INT", both of which should compare without issue to "ModuleLevelId" Which part of this is incorrect? – pwmusic Sep 20 '12 at 20:23
  • This corected `(ISNULL(ModuleLevelId) && !ISNULL(LEV_CODE)) || (!ISNULL(ModuleLevelId) && ISNULL(LEV_CODE)) || (ISNULL(LEV_CODE) ? 0 - ModuleLevelId : (DT_I4)(LEV_CODE)) != ModuleLevelId` condition gives me results `30 20 TRUE`, `NULL 40 TRUE`, `50 NULL TRUE`, `30 30 False` (LEV_CODE, ModuleLevelId, Derived column boolean data type ). So condition works this what you want? – Justin Sep 20 '12 at 21:09

1 Answers1

4
(ISNULL(ModuleLevelId) && !ISNULL(LEV_CODE)) || 
(!ISNULL(ModuleLevelId) && ISNULL(LEV_CODE)) ||
((ISNULL(LEV_CODE) ? 0 - ModuleLevelId :(DT_I4)LEV_CODE) != ModuleLevelId)

I think you need to surround your third term: 

 (ISNULL(LEV_CODE) ? 0 - ModuleLevelId :(DT_I4)LEV_CODE) != ModuleLevelId

with (). I think the expression engine is parsing it incorrectly as 

((ISNULL(ModuleLevelId) && !ISNULL(LEV_CODE)) || 
(!ISNULL(ModuleLevelId) && ISNULL(LEV_CODE)) ||
(ISNULL(LEV_CODE) ? 0 - ModuleLevelId :(DT_I4)LEV_CODE)) != ModuleLevelId

or (Boolean || Boolean || INT) != INT

when what you want is (Boolean || Boolean || Boolean)

Try the topmost code in this answer.

William Salzman
  • 6,396
  • 2
  • 33
  • 49