Domain query OR condition limitation

Issue:

In the release notes of v6x, it states that you can use OR condition in domain query by manually updating the schema join query. However, there are limitations. See below.

From the release notes:

* Domains Complex Joins - We have enhanced our join functionality to include complex operators (listed below). This feature is only available by directly editing the Domain XML file; once the Domain XML is edited, you can't use the Domain designer to edit the Domain. Complex joins: - Can use constants for comparisons in join expressions. - Support more types of operators beyond equality (=), including !=, >,<,<=,>=, IN, and NOT IN. - Support new specification of join types (inner join, left outer join, and right outer join). - Address a previous issue of OR joins not being recognized properly.

Resolution

Advanced joins must be specified in different elements (tableRefList and joinList). Anything using joinedDataSetList is going to be subject to the limits of the original schema.

For example, the second join makes reference to three tables (dbo_EMP, dbo_SA_REPORT, dbo_SCHEDULE_S_REPORT), but advanced joins can only reference two tables in the join expression. You can get the OR to work by switching to tableRefList and joinList, but you will still not be able to join two tables at once; you have to explore other ways of doing this, either with a derived table, or perhaps by defining a view which will show the two tables as one. full outer join dbo_SCHEDULE_SHIFT_REPORT dbo_SCHEDULE_SHIFT_REPORT on (dbo_SA_REPORT.PDATE == dbo_SCHEDULE_SHIFT_REPORT.PDATE) left outer join dbo_EMP dbo_EMP on (dbo_SA_REPORT.PAYROLL == dbo_EMP.PAYROLL or dbo_SCHEDULE_SHIFT_REPORT.PAYROLL == dbo_EMP.PAYROLL)

Ref. Case #01438777