I created a count of a subquery which ran fine when I ran it in SPUFI. The query tries to count the number of unique rows based on two columns. It is written like so:
SELECT DISTINCT table1.key1, table1.key2
WHERE TBL1_REF_NBR = TBL2_REF_NBR
My requirement is to run it in a COBOL program.
At first, I tried to test it with a batch job and got SQLCODE: -4743 error. This problem was fixed by adding the line:
SET CURRENT APPLICATION COMPATIBILITY='V11R1';
However, when I added the query to the COBOL and tried to compile, I get an error when binding which shows the same SQLCODE -4743, regardless of whether I added the SET CURRENT APPLICATION COMP... line.
I find it odd that a simple count on a subquery is not compatible with the current level when subselects are all over the DB2 documentations and they do not indicate a minimum DB2 level. It is also odd that when looking at JES output of the compiler job, I see the DB2 precompiler is V11R1 and found NEWFUN(V11). I did not find anything for APPCOMPAT though.
Am I missing something with my or is this query really too complex for my shop?
Thank you all.