I was recently working with a power user at one of our sites and I was asked why a conditional expression they had created wasn't working. The user had previously used the SQL statement in a query, but now wanted to use it as a restriction for the purchase requests. The goal was to restrict users changing a purchase request to 'APPR' status if the PR had a line item that was using a decimal quantity with the UOM of 'EA' - e.g 2.32 EA. ¹

The conditional expression looked like this:

not exists(select 1 from prline where prnum=pr.prnum and siteid=pr.siteid and orderunit = 'EA' and (orderqty % 1 <> 0))

At first glance it appeared to be correct. I used the expression as part of a query and it returned the expected results of several PR's that did not have any lines with 2.32 EA. So I went into the Conditional Expression app to verify that the statement was valid. Maximo validated the statement as correct and let me save the conditional expression. The problem was when the same statement was applied as a conditional expression on the security group, the expected restricted results were not occurring. Users were still given the opportunity to change the PR to 'APPR' status.

After leaving it alone for the afternoon, I came back and tried to look at why the conditional expression wasn't working as planned. After looking at other ways I could write the expression I remembered that Conditional Expressions don't work like Where Clause queries. While Where Clauses, like the one the user provided, need to build table relationships on standard SQL, Conditional Expressions uses a syntax that looks like SQL. Conditional Expressions uses a colon : to reference the record of the current business object. In my case, I was doing a subselect and needed to reference back to the parent PR object.

Updating the expression to this:

not exists(select 1 from prline where prnum=:prnum and siteid=:siteid and orderunit = 'EA' and (orderqty % 1 <> 0))

Allowed the conditional expression to correctly restrict a user from changing the status from WAPPR to APPR status.

The proper method should be:

• Where Clause: prnum = pr.prnum
• Conditional Expression: prnum = :prnum

Just a friendly reminder that syntax can make or break your expressions.