Conditional "Where Clauses"

[Will.Hampton]

I've done quite a bit of work tracking down through what's actually happening in the condition nodes (and conditional assignments) where clause and what it actually does. In versions 4.x and early 5.x it for sure just gets appended to the query and sent to the dB as is ...

For version 6.2.x and probably 6.x and 6.1.x the "parser" actually can resolve the "where" condition prior to sending it to the dB - meaning that you're code doesn't have to be as carefully written and that the system can respond faster to the Node.

This is true unless you...

[Will.Hampton]

Figured I'd add more since nobody asked <smile>

Within Maximo’s Workflow functionality it is possible to write queries that allow for conditional routing. When these are ran Workflow resolves them to “true” or “false” and routes the record based on these results.

Maximo v6.x improves (or fixes ?) this functionality by performing an interpretive check prior to sending the record to the database for resolution, IF it can make a determination of whether or not the result is true or false – the record is never sent to the database. IF it cannot make a determination due to the complexity of the statement or the use of an embedded “select” statement it must send the record to the database for resolution. In this case the statement is sent as:

select * from <tablename> where <condition (where clause)>

If the table is large and the condition is true – the entire contents of the table are returned. This functionality is desired and was developed this way so the Workflows can run at the System, Organization or Site levels as necessary – it is up to the Workflow Developer to add the appropriate filtering to get it right.

In the two examples below you can see the developer doing it right and a badly formatted clause for reference:

Mostly Right: (row 1)

wonum = :wonum and siteid = :siteid and :user in(select respparty from persongroupteam where useforsite = :siteid and persongroup in(select persongroup from persongroup where type='ENG'))

NOTE: in this example (above) type is a custom field.

Wrong: (row 2)

:user in (select respparty from persongroupteam where resppartygroup = (select respparty from persongroupteam where persongroup = :persongroup and useforsite = :siteid and sitedefault=1))

Both of these example “where clauses” are in Workflows that query the Work Order table. In the first example you can clearly see the filtering being done before the record is sent to the database “wonum = :wonum and siteid = :siteid”. This syntax ensures that the result returned will be based only on the single underlying record and not all the records in the Work Order (workorder) table. The parameter :user refers to the currently logged in user so no indexing on this would help; additionally we may want to investigate adding an index to the persongroup table for the type field, however that is very minor in comparison to the potential impact of the where clause construction.

NOTE: I stated above that row 1 was "mostly right" - this query should include a "woclass = " statement as it is possible to have the same wonum on multiple records within the same site; they are separated by "woclass". Possible classes within Maximo (system defaults)include "WORKORDER", "RELEASE", "CHANGE", and 'ACTIVITY'.

NOTE: There is one case where utilizing a “poorly constructed” where clause cannot be avoided and that is on creation of a new record that has yet to be saved. IF the record hasn’t been saved yet, then it is impossible to restrict the first part of the call (as there is nothing yet to filter on…).

It is entirely possible that row 2 is exactly that type of condition – however this type of construction should be avoided any way possible, especially since as coded above it will attempt to query ALL the Work Orders in the system (spanning all the Organizations and Sites, including Workorders, Problems, Activities, Changes, and Releases (types of Work Orders)). This is due to the fact that Workflow is a System Level task as described above.