7/23/2019 Correct Syntax for Sub-selects in Conditional Expressions - Tivoli Blog

1/1

Correct syntax for sub-selects in Conditional Expression

Nov 10 Posted by Jason Verlyin Uncategorized

was recently working with a power user at one of our sites and I was asked why a conditional expression they had created wasnt working. Theser 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 restsers 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. [1]

The conditional expression looked like this:

ot exists(select 1 from prline where prnum=pr.prnum and siteid=pr.siteid and orderunit = 'EA' and (orderqty % 1

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 PRs that did notave any lines with 2.32 EA. So I went into the Conditional Expression app to verify that the statement was valid. Maximo validated the statemes correct and let me save the conditional expression. The problem was when the same statement was applied as a conditional expression on tecurity 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 wasnt working as planned. After looking other ways I could write the expression I remembered that Conditional Expressions dont work like Where Clause queries. While Where Clause

ke theone the user provided, needto 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 eference back to the parent PR object.

Updating the expression to this:

ot 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.prnumConditional Expression: prnum = :prnum

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

1. The reason for the restriction was to prevent PRs from getting processed and causing confusion when invoice processing occurred. The

goal was prevent the question on why the PO said 2.32 EA and the invoice said 2.32 LB, 2.32 HR, 2.32 KG, etc.

Tags: Conditional Expression, Maximo, Syntax

PRINT

http://%20void%280%29/http://www.tivoli-ug.org/index.php/blogs/tags/tag/conditional-expressionhttp://www.tivoli-ug.org/index.php/blogs/tags/tag/maximohttp://www.tivoli-ug.org/index.php/blogs/tags/tag/syntaxhttp://tivoli-ug.org/community-blogs/b/weblog4/archive/2014/11/10/correct-syntax-for-sub-selects-in-conditional-expressions.aspx#fnref:1http://www.tivoli-ug.org/index.php/my-profile/871-jason-verlyhttp://www.tivoli-ug.org/index.php/blogs/tags/tag/syntaxhttp://tivoli-ug.org/community-blogs/b/weblog4/archive/2014/11/10/correct-syntax-for-sub-selects-in-conditional-expressions.aspx#fn:1http://www.tivoli-ug.org/index.php/blogs/tags/tag/maximohttp://tivoli-ug.org/community-blogs/b/weblog4/archive/2014/11/10/correct-syntax-for-sub-selects-in-conditional-expressions.aspx#fnref:1http://www.tivoli-ug.org/index.php/blogs/tags/tag/conditional-expressionhttp://www.tivoli-ug.org/index.php/blogs/categories/listings/uncategorizedhttp://%20void%280%29/