The best way to resolve SQL error -305, simplified

You can write SQL query without using Null indicator. Since handling Nulls is so important when aggregate functions are part of your SQL Query.

Any SQL statement that contains one of the following aggregate functions should have a Null-Indicator host variable as part of the select (MIN MAX, AVG, SUM). DB2 will return a null indicator to the program if it finds no data to process these  functions, and the COBOL program will have to define a null indicator.

If the program is not set up with a null indicator, an invalid -305 SQLCODE is returned. It is preferable to code the VALUE, COALESCE, or IFNULL function to alleviate any null indicator logic.

For example:

SELECT IFNULL(AVG(SALARY), 0) 

FROM EMP 

WHERE WORKDEPT = 'XYZ'

This will either return the average if rows are found or a zero if no rows were met in order to calculate an average.

Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.