No rows returned from Table Procedure

Products

• IDMS
• IDMS - Database
• IDMS - ADS

Issue/Introduction

When I use the WHERE clause syntax for invoking my table procedure, I am seeing no rows returned when I know there are some there.
If I use the Table Procedure reference syntax to pass the same parameters, I see the expected rows.
Why is this?

Environment

CA IDMS all supported releases.

Cause

There is a subtle but significant difference in the calling methods which is the reason for this behaviour.

Using the traditional "WHERE" syntax, the criteria in that WHERE clause is automatically applied to the rows on output regardless of what the underlying table procedure program does.
Using the Table Procedure Reference syntax - "( COLUMN1 = 'VALUE', COLUMN2 = 1, etc )"  - on the table procedure reference, that does not happen.

To illustrate, SQL.DEPT_TP is a simple table procedure which returns the rows in the DEPARTMENT record of the demonstration database. If a DEPT_ID is passed, it returns only that row, otherwise it sweeps the area and returns every row.

Note:

SELECT DEPT_ID, DEPT_NAME FROM SQL.DEPT_TP WHERE DEPT_ID=1000;
*+                                            
*+ DEPT_ID  DEPT_NAME                         
*+ -------  ---------                         
*+    1000  PERSONNEL                         
*+                                            
*+ 1 row processed

        
If we add DEPT_NAME='PERSONNEL' to the WHERE clause, the table procedure makes no reference to it as an input parameter, but it is used by the CA-IDMS SQL option as a filter for the data on output.

SELECT DEPT_ID, DEPT_NAME FROM SQL.DEPT_TP
WHERE DEPT_ID=1000 AND DEPT_NAME='PERSONNEL';
*+                                            
*+ DEPT_ID  DEPT_NAME                         
*+ -------  ---------                         
*+    1000  PERSONNEL                         
*+                                            
*+ 1 row processed

If we use a different value for DEPT_NAME while retaining the original DEPT_ID value, it has no impact on how the program executes because the parameter it considers for input - DEPT_ID - is the same, but the DEPT_NAME value is used to filter the output, and as it's different (XXX <> PERSONNEL), that row is excluded from the output:

SELECT DEPT_ID, DEPT_NAME FROM SQL.DEPT_TP
WHERE DEPT_ID=1000 AND DEPT_NAME='XXX';       
*+                                            
*+ No qualifying rows found

However, if you use the Table Procedure Reference syntax, those values passed are not used to filter the output and so the original row is returned:

SELECT DEPT_ID, DEPT_NAME FROM SQL.DEPT_TP
(DEPT_ID=1000, DEPT_NAME='XXX');              
*+                                            
*+ DEPT_ID  DEPT_NAME                         
*+ -------  ---------                         
*+    1000  PERSONNEL                         
*+                                            
*+ 1 row processed

This behaviour is documented here: https://docops.ca.com/ca-idms-ref/19/en/sql-reference/defining-and-using-table-procedures/table-procedure-parameters (scroll down to the heading "Difference Between Table Procedure Reference and WHERE Clause").

Resolution

This is the documented behaviour of the software.
The only resolutions are to use whichever form of the syntax meets the needs of the application, or re-write the table procedure program accordingly.

Additional Information

Difference Between Table Procedure Reference and WHERE Clause