DQ180I - QUERY EXECUTION WAS STOPPED BECAUSE NO ROWS WERE FOUND

book

Article ID: 129156

calendar_today

Updated On:

Products

CA Datacom CA DATACOM - AD CA CIS CA Common Services for z/OS CA 90s Services CA Database Management Solutions for DB2 for z/OS CA Common Product Services Component CA Common Services CA Datacom/AD CA ecoMeter Server Component FOC CA Easytrieve Report Generator for Common Services CA Infocai Maintenance CA IPC Unicenter CA-JCLCheck Common Component CA Mainframe VM Product Manager CA Chorus Software Manager CA On Demand Portal CA Service Desk Manager - Unified Self Service CA PAM Client for Linux for zSeries CA Mainframe Connector for Linux on System z CA Graphical Management Interface CA Web Administrator for Top Secret CA CA- 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
 

Cause

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

Environment

Z/OS, CA DATACOM/DB 14.0 and higher

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.