A Datacom Dataquery DQL query is returning incorrect results when using two fields of a compound group field in a RELATED BY clause.
If a single field or a group field is used in the RELATED BY clause it returns the correct results.
Example:
Group field group-col is a key in both tables and consists of fields col1 and col2.
FIND ALL table1 ROWS
RELATED BY col1 <===== single field of group field returns correct result
TO table2
FIND ALL table1 ROWS
RELATED BY group-col <===== Group field returns correct result
TO table2
FIND ALL table1 ROWS
RELATED BY col1 col2 <===== Two Key fields in the Group returns incorrect results
TO table2
Release : All releases
Component: Dataquery for CA Datacom
The problem is caused by specifying 2 columns in the RELATED BY clause.
When relating 2 tables with a common column/key you can only specify one column/key name in the RELATED BY clause.
There's another feature in Dataquery that allows you to specify a complex relationship expression, when you have the same key or column in two tables but with different names.
In this case you do specify 2 column names in the RELATED BY clause, see documentation section Complex Relationships - No Common Key
When you do not have a common key in each table, you can use relationship clauses to establish a complex relationship between two types of tables by relating a column, key or value contained in the primary table to a key or column in the secondary table.
The syntax for a complex relationship expression is:
FIND ALL table1 ROWS
RELATED BY column1 column2
TO table2
Where column1 is the column name in table 1 and
column2 is the same data in table 2 but with a different column name.
However, in the syntax that is not working in the introduction above it is not a complex relationship because the fields exist in both tables with the same names:
FIND ALL table1 ROWS
RELATED BY col1 col2
TO table2
It is relating column col1 in table1 to column col2 in table2 which are different fields containing different data, that is why incorrect results are returned.