Where clause selection
A query can search for records based on a SQL 'Where' clause that can be provided as part of the integration query XML. The Where clause offers support for more complex queries and the querying of classification-related data.
The use of the 'Where' clause is mutually exclusive from the use of individual elements. If a query request provides data for the Where element and other elements in the XML, only the WHERE element is used in the query execution.
The WHERE element is located on the same level as the top-level object in an object structure (purchase order), as in the following example:
<QueryMXPO xmlns="http://www.ibm.com/maximo">
<MXPOQuery>
<PO> </PO>
<WHERE> </WHERE>
</MXPOQUERY>
</QueryMXPO>
The WHERE element is available in the schema only when the operation is Query and can exist only once. To join multiple tables in the query, use ‘exists' within the Where clause, in the same manner as the WHERE clause from the List tab of an application. The following sample query, using the MXPO object structure, retrieves purchase orders that have a PO Line for Item number 1002:
<QueryMXPO xmlns="http://www.ibm.com/maximo">
<MXPOQuery>
<WHERE>(siteid = 'BEDFORD') and (exists (select 1 from maximo.poline where
(i temnum='1002') and (ponum=po.ponum and revisionnum=po.revisionnum and
siteid=po.siteid)))</WHERE>
</MXPOQUERY>
</QueryMXPO>
The result of this query retrieved two purchase orders (not all elements are included in the example):
<?xml version="1.0" encoding="UTF-8"?>
<QueryMXPOResponse xmlns="http://www.ibm.com/maximo" xmlns:xsi="http://www.w3.org/
2001/XMLSchema-instance" creationDateTime="2012-03-21T11:20:13-04:00"
transLanguage="EN" baseLanguage="EN" messageID="1332343214013260273" maximoVersion=
"7 5 20110413-2230 V7500-721" rsStart="0" rsTotal="2" rsCount="2">
<MXPOSet>
<PO>
<DESCRIPTION>Window and installation for office building</DESCRIPTION>
<ORDERDATE>2000-04-20T14:00:00-04:00</ORDERDATE>
<ORGID>EAGLENA</ORGID>
<PONUM>1013</PONUM>
<POTYPE>STD</POTYPE>
<VENDOR>JK</VENDOR>
.
.
.
<POLINE>
<DESCRIPTION>5 ft. X 6 ft. window pane</DESCRIPTION>
<ITEMNUM>1002</ITEMNUM>
<ITEMSETID>SET1</ITEMSETID>
<ORDERQTY>1.0</ORDERQTY>
<POLINENUM>1</POLINENUM>
.
.
.
<POCOST>
<COSTLINENUM>1</COSTLINENUM>
.
.
.
</POCOST>
</POLINE>
<POLINE>
<DESCRIPTION>Installation of window pane</DESCRIPTION>
<ITEMNUM />
<ITEMSETID>SET1</ITEMSETID>
<ORDERQTY>6.0</ORDERQTY>
<POLINENUM>2</POLINENUM>
.
.
.
<POCOST>
<COSTLINENUM>1</COSTLINENUM>
.
.
.
</POCOST>
</POLINE>
</PO>
<PO>
<DESCRIPTION>Window and installation for Office Building</DESCRIPTION>
<ORDERDATE>2003-02-27T10:07:24-05:00</ORDERDATE>
<ORGID>EAGLENA</ORGID>
<PONUM>1029</PONUM>
<POTYPE>STD</POTYPE>
<VENDOR>JK</VENDOR>
.
.
.
<POLINE>
<DESCRIPTION>5 ft. X 6 ft. window pane</DESCRIPTION>
<ITEMNUM>1002</ITEMNUM>
<ITEMSETID>SET1</ITEMSETID>
<ORDERQTY>1.0</ORDERQTY>
<POLINENUM>1</POLINENUM>
.
.
.
<POCOST>
<COSTLINENUM>1</COSTLINENUM>
.
.
.
</POCOST>
</POLINE>
<POLINE>
<DESCRIPTION>Installation of window pane</DESCRIPTION>
<ITEMNUM />
<ITEMSETID>SET1</ITEMSETID>
<ORDERQTY>6.0</ORDERQTY>
<POLINENUM>2</POLINENUM>
.
.
.
<POCOST>
<COSTLINENUM>1</COSTLINENUM>
.
.
.
</POCOST>
</POLINE>
</PO>
</MXPOSet>
</QueryMXPOResponse>