DQ180I - QUERY EXECUTION WAS STOPPED BECAUSE NO ROWS WERE FOUND

Products

• Datacom
• DATACOM - AD
• CIS
• COMMON SERVICES FOR Z/OS
• 90S SERVICES
• DATABASE MANAGEMENT SOLUTIONS FOR DB2 FOR Z/OS
• COMMON PRODUCT SERVICES COMPONENT
• Common Services
• CA ECOMETER SERVER COMPONENT FOC
• Easytrieve Report Generator for Common Services
• INFOCAI MAINTENANCE
• IPC
• UNICENTER JCLCHECK COMMON COMPONENT
• Mainframe VM Product Manager
• CHORUS SOFTWARE MANAGER
• CA ON DEMAND PORTAL
• CA Service Desk Manager - Unified Self Service
• PAM CLIENT FOR LINUX ON MAINFRAME
• MAINFRAME CONNECTOR FOR LINUX ON MAINFRAME
• GRAPHICAL MANAGEMENT INTERFACE
• WEB ADMINISTRATOR FOR TOP SECRET
• Xpertware

Issue/Introduction

The following query gives incorrect result(s):
FIND ALL ATS120-FPL
WHERE ACCTNO = '0042580' AND FY = '2019' AND LIEN-CODE = 'C'

RELATED BY ACCTNO TO ATS120-INV
WHERE ATS120-INV.FY = '2019'
AND ATS120-INV.INVNO = ATS120-FPL.INVNO

RELATED BY ACCTNO TO ATS116-PAY
WHERE ATS116-PAY.FY = '2019'
AND ATS116-PAY.INVNO = INVNO

PRINT
FROM ATS120-FPL FY ACCTNO INVNO LIEN-CODE
FROM ATS120-INV AMOUNT STATUS
FROM ATS116-PAY BATCH-DATE
 

Environment

Z/OS, CA DATACOM/DB 14.0 and higher

Cause

Invalid DQL syntax request.
Request should follow the "Repeating Relationship" logic.

Resolution

FIND ALL ATS120-FPL 
WHERE ACCTNO = '0042580' AND FY = '2019' AND LIEN-CODE = 'C' 

RELATED BY ACCTNO TO ATS120-INV 
WHERE ATS120-INV.FY = '2019' 
AND ATS120-INV.INVNO = ATS120-FPL.INVNO 

AND ATS120-FPL RELATED BY ACCTNO TO ATS116-PAY <=== changed 
WHERE ATS116-PAY.FY = '2019' 
AND ATS116-PAY.INVNO = ATS120-FPL.INVNO <=== changed

PRINT
FROM ATS120-FPL FY ACCTNO INVNO LIEN-CODE
FROM ATS120-INV AMOUNT STATUS
FROM ATS116-PAY BATCH-DATE 

Additional Information

From Documentation (Repeating Relationship)
https://docops.ca.com/ca-datacom/15-1/en/reference/ca-dataquery-reference/dql-statements/relationship-clauses-dql#RelationshipClauses(DQL)-SimpleRelationshipwithaCommonKey
https://docops.ca.com/ca-datacom/15-1/en/reference/ca-dataquery-reference/dql-statements/relationship-clauses-dql
The related information is near the bottom. (Deciding Which Type of Relationship to Use) - there are some examples for each type..

Extracted from:

Repeating Relationships

A repeating relationship occurs when the name of a table is repeated as the subject of a relationship. In general, a repeating relationship should be used when the subject of the relationship wanted is not the object of the last relationship specified.

Example

The following FIND statement uses the common key CUST-ID-KEY to connect the CAI-CUST-REC table with the CAI-DETAIL-REC table and the CAI-ORDERS-REC table in a repeating relationship.

 FIND 10 CAI-CUST-REC ROWS

   RELATED BY CUST-ID-KEY TO

      CAI-DETAIL-REC ROWS

   AND CAI-CUST-REC RELATED BY CUST-ID-KEY

      TO CAI-ORDERS-REC ROWS

         WITH ORD-YR = '87'

The preceding example contains two RELATED BY statements. This repetition indicates that a relationship must exist between more than two tables before a logical row is selected. In the example, the relationship of three tables is established.