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
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
SELECT DEPT_ID, DEPT_NAME FROM SQL.DEPT_TP WHERE DEPT_ID=1000 AND DEPT_NAME='XXX'; *+ *+ No qualifying rows found
SELECT DEPT_ID, DEPT_NAME FROM SQL.DEPT_TP (DEPT_ID=1000, DEPT_NAME='XXX'); *+ *+ DEPT_ID DEPT_NAME *+ ------- --------- *+ 1000 PERSONNEL *+ *+ 1 row processed