How to avoid a cartesian product in a where clause and error message AHD03106

book

Article ID: 50056

calendar_today

Updated On:

Products

CA IT Asset Manager CA Software Asset Manager (CA SAM) ASSET PORTFOLIO MGMT- SERVER SUPPORT AUTOMATION- SERVER CA Service Desk Manager - Unified Self Service KNOWLEDGE TOOLS CA Service Management - Asset Portfolio Management CA Service Management - Service Desk Manager

Issue/Introduction

When defining the where clause for a data partition or for a stored query, Service Desk may detect and report a cartesian product error condition. In some cases, the where clause can be re-written to avoid the cartesian product.

An example is provided in the Solution section that follows.

 

Environment

Release: UAPMAC990JPP-12.6-Asset Portfolio Management-Asset Configuration
Component:

Resolution

Where clauses may be used to define data partitions and stored queries in Service Desk.

A where clause of the form "A OR B" may result in a cartesian product error situation.

You may be able to avoid the error situation by re-defining a where clause of the form "A OR B" as follows:

(A AND B) OR (A AND NOT B) OR (NOT A AND B)

For example, for the following where clause for a Data Partition, Service Desk returns the error message "AHD05800:Bad where clause. AHD03106:Where clause results in a Cartesian product":

    chg_ref_num LIKE '123' OR (requestor.organization = @root.organization)  

To avoid the Cartesian product error situation, you could re-write the where clause as follows:

    ((chg_ref_num LIKE '123') AND (requestor.organization = @root.organization)) OR
    ((chg_ref_num NOT LIKE '123') AND (requestor.organization = @root.organization)) OR
    ((chg_ref_num LIKE '123') AND (requestor.organization != @root.organization))