IF statements and order

[justlrng]

I am just wondering if anyone has ever had an issue with the IF statement and the ordering of the clauses within the IF statement. On several occassions, I have tried to debug some of my jobs and the problem ended up being the order of the comparisons I was making in an IF statement.

For example

If TRIM(olePersonRoleIn.tchr_ref_nr) <> TRIM(UpdateTchrRefNr) OR TRIM(olePersonRoleIn.tchr_cert_nr) <> TRIM(UpdateTchrCertNr) OR TRIM(olePersonRoleIn.ait_id) <> TRIM(UpdateAITId) OR olePersonRoleIn.ab_ed_id <> UpdateASN OR olePersonRoleIn.deceased_fl <> UpdateDeceasedFl Or olePersonRoleIn.given_nm <> UpdateGivenNm Or olePersonRoleIn.middle_nm <> UpdateMiddleNm Or olePersonRoleIn.sur_nm <> UpdateSurNm Or olePersonRoleIn.person_nm_title_ty <> UpdateNmTitleTy Or olePersonRoleIn.person_role_ty <> UpdatePersonRoleTy Or olePersonRoleIn.protected_fl <> If UpdateProtectedFl = "U" Then olePersonRoleIn.protected_fl Else UpdateProtectedFl Then "T" Else "F"

this statement works but if I order the comparisons in a slightly different way,

If TRIM(olePersonRoleIn.tchr_ref_nr) <> TRIM(UpdateTchrRefNr) OR TRIM(olePersonRoleIn.tchr_cert_nr) <> TRIM(UpdateTchrCertNr) OR olePersonRoleIn.ab_ed_id <> UpdateASN OR olePersonRoleIn.deceased_fl <> UpdateDeceasedFl Or olePersonRoleIn.given_nm <> UpdateGivenNm Or olePersonRoleIn.middle_nm <> UpdateMiddleNm Or olePersonRoleIn.sur_nm <> UpdateSurNm Or olePersonRoleIn.person_nm_title_ty <> UpdateNmTitleTy Or olePersonRoleIn.person_role_ty <> UpdatePersonRoleTy Or olePersonRoleIn.protected_fl <> If UpdateProtectedFl = "U" Then olePersonRoleIn.protected_fl Else UpdateProtectedFl OR TRIM(olePersonRoleIn.ait_id) <> TRIM(UpdateAITId) Then "T" Else "F"


then I find this evaluates to "T" when I expect "F".

As I stated, this is not the first time that this has happened and am curious to know if anyone else has experienced similiar problems and if there is a solution to this.

Thanks

[chulett]

You might want to explain what "if I order the comparisons in a slightly different way" means exactly. An example would be good.

[ArndW]

Justlrng,

You are getting an error of your OR's evaluating to true when you think they shouldn't; so [in addition to putting in some parenthesis first for clarity] you can remove one condition at a time until you get the expected row result, then you've found the culprit. With the data there isn't too much for us to do here.

The conditions are evaluated left-to-right but no optimization is done (i.e. the evaluation does not stop when the first true is found, even if all conditions are OR'd together)

[PhilHibbs]

I am just wondering if anyone has ever had an issue with the IF statement and the ordering of the clauses within the IF statement. On several occassions, I have tried to debug some of my jobs and the problem ended up being the order of the comparisons I was making in an IF statement.

Try putting some parentheses round the embedded if:

If TRIM(olePersonRoleIn.tchr_ref_nr) <> TRIM(UpdateTchrRefNr) OR TRIM(olePersonRoleIn.tchr_cert_nr) <> TRIM(UpdateTchrCertNr) OR TRIM(olePersonRoleIn.ait_id) <> TRIM(UpdateAITId) OR olePersonRoleIn.ab_ed_id <> UpdateASN OR olePersonRoleIn.deceased_fl <> UpdateDeceasedFl Or olePersonRoleIn.given_nm <> UpdateGivenNm Or olePersonRoleIn.middle_nm <> UpdateMiddleNm Or olePersonRoleIn.sur_nm <> UpdateSurNm Or olePersonRoleIn.person_nm_title_ty <> UpdateNmTitleTy Or olePersonRoleIn.person_role_ty <> UpdatePersonRoleTy Or olePersonRoleIn.protected_fl <> (If UpdateProtectedFl = "U" Then olePersonRoleIn.protected_fl Else UpdateProtectedFl) Then "T" Else "F"

Personally, I would have made that embedded if into a stage variable (or just a regular variable if this is in a routine not a derivation).

Sorry, my mistake, I misunderstood your post. It's the second one that's going wrong, isn't it? Well, the parentheses will fix that. Your code as posted is being executed as this:

If TRIM(olePersonRoleIn.tchr_ref_nr) <> TRIM(UpdateTchrRefNr) OR TRIM(olePersonRoleIn.tchr_cert_nr) <> TRIM(UpdateTchrCertNr) OR olePersonRoleIn.ab_ed_id <> UpdateASN OR olePersonRoleIn.deceased_fl <> UpdateDeceasedFl Or olePersonRoleIn.given_nm <> UpdateGivenNm Or olePersonRoleIn.middle_nm <> UpdateMiddleNm Or olePersonRoleIn.sur_nm <> UpdateSurNm Or olePersonRoleIn.person_nm_title_ty <> UpdateNmTitleTy Or olePersonRoleIn.person_role_ty <> UpdatePersonRoleTy Or olePersonRoleIn.protected_fl <> If UpdateProtectedFl = "U" Then olePersonRoleIn.protected_fl Else (UpdateProtectedFl OR TRIM(olePersonRoleIn.ait_id) <> TRIM(UpdateAITId)) Then "T" Else "F"

It seems that OR binds with a higher precedence than ELSE. Always always always clarify your expressions with parentheses.

[kduke]

Do it in a routine. Nested case statements would be lots easier to read and modify later.

[justlrng]

Thanks for all your help. It seems to be just an issue that I have. What I have found also works is to place portions of the statement in yet another stage variable and evaluate it there.

Thanks.