I am trying to understand how common table expressions (CTEs) I am trying to figure out why I am getting errors related to the following:
Details: ADO Error Code: 0x
Source: IBMDA400 Command
Description: PWS9801 Function rejected by user exit program SAFENET in PCSECLIB
Cause: User exit program SAFENET in library PCSECLIB called for the function and returned an indication that the function should not be done.
The SQL code behind this is trivial:
with cte1 (my_text,my_numcte1) as
select 'hello world1',1 from sysibm.sysdummy1 union
select 'hello world2',2 from sysibm.sysdummy1
select * from a73com.f0005
cte2 (my_numcte2,new_text,additional_text) as
select 1,'goodbye1','hello again1' from sysibm.sysdummy1 union
select 2,'goodbye2','hello again2' from sysibm.sysdummy1
select cte1.* ,cte2.*
from cte1 inner join cte2 on cte1.my_numcte1=cte2.my_numcte2
Note that I have commented out a section, so this query is selecting everything from the sysibm.sysdummy1 tables.
I get the error above for this seemingly trivial query.
When I remove the comment indicators by inserting another CTE that actually queries against a real library and table, then the query runs fine and returns the following:
MY_TEXT MY_NUMCTE1 MY_NUMCTE2 NEW_TEXT ADDITIONAL_TEXT
hello world1 1 1 goodbye1 hello again1
hello world2 2 2 goodbye2 hello again2
would anyone know why there has to be a we cannot create multiple CTEs that select from sysibm.sysdummy1 without having some other type of query against a real library and table?
For some more complex queries, where I am joining on real tables and also the sysibm.sysdummy1 table, (in some cases up to 12 CTEs linked together through various types of joins) I am seeing errors like the following:
Description: SQL0901: SQL System Error
Cause: A SQL system error has occurred. The current sql statement cannot be completed successfully. The error will not prevent other SQL statements from being processed. Previous messages indicate that the there is a problem with the SQL statement and SQL did not correctly diagnose the error. The previous message identifier was CPF4204. Internal error type 3002 has ocurred...
Anyone know what is happening? Similar queries on the MS-SQL server side using CTEs run fine.