DQL RELATED BY with 2 fields of compound group field returns incorrect results

Products

• Datacom
• Datacom/DB
• Datacom/AD

Issue/Introduction

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

 

Environment

Release : All releases

Component: Dataquery for CA Datacom

Resolution

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.