Help. SQLCODE -4743 when trying to count a subselect

Antonio Andrew

Help. SQLCODE -4743 when trying to count a subselect

Hi all,

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 COUNT(*)                                      
  FROM(                                              
    SELECT DISTINCT table1.key1, table1.key2
      FROM table1,                        
           table2                         
     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.

Joe Geller

RE: Help. SQLCODE -4743 when trying to count a subselect
(in response to Antonio Andrew)

Hi,

First,

SET CURRENT APPLICATION COMPATIBILITY='V11R1';    is for dynamic SQL.  If you are using static SQL in your COBOL program, then you need the applcompat Bind option.

As for why you got the error for such as simple query, I think it is that in earlier versions of DB2, the nested table expression would need a correlation name   i.e. select count(*) from (select...........) as KEYS

I could be wrong, but perhaps that requirement has been lifted in V11?

Joe


In Reply to Antonio Andrew:

Hi all,

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 COUNT(*)                                      
  FROM(                                              
    SELECT DISTINCT table1.key1, table1.key2
      FROM table1,                        
           table2                         
     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.

Antonio Andrew

RE: Help. SQLCODE -4743 when trying to count a subselect
(in response to Joe Geller)

Adding an alias to the subquery worked! Thanks a lot for the quick response Joe.