RE: SQL0305 Message in 5000 page joblog

Does a null have a different meaning in your context than a zero (i.e. will you treat them differently)? If not, I'd go COALESCE.



-----Original Message-----
From: Don Brown [mailto:DBrown@xxxxxxxxxx]
Sent: Friday, September 29, 2017 10:10 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: SQL0305 Message in 5000 page joblog

System is V7R2 and was current on PTF's as at June 30 2017

A batch process that runs is generating a 5000+ joblog with message
SQL0305 repeated thousands of times.

Is this just indicating the SQL select simply did not return any records and as such SQLTOTAMT is NULL or ?

To cater for this there seems to be a couple of options ...

H ALWNULL(*INPUTONLY)

or

Using COALESCE. I am not a SQL guru and have not used this option so do I use like;

select COALESCE(sum(uddamt + uddaoa + uddeam - uddcct - uddbkf), 0) into :sqlToaAmt

Is one option any better or more correct than the other ?

Or

Should I define indicator variable for the sqlTotAmt ?



Details of the message are below;

The embedded SQL is like this;

sqlTotAmt = 0;
exec sql
select sum(uddamt + uddaoa + uddeam - uddcct - uddbkf)
into :sqlTotAmt
from dktuddpf, dktlocpf, dktldtpf, dktldppf
where uddac = :entac and uddpod = 0
and loccoy = :entcoy and uddloc = locloc
and uddloc = ldploc and uddldt = ldtldt
and ldppop in ('1','3')
and uddsts not in ('D','I') and uddddc = ' '
and (ldpopc <> '1' or
(ldpopc = '1' and
udddfn in (select focdfn from dktfocpf
where focsts = 'A'
and focloc = uddloc
and focldt = uddldt)))
and ((ldtldg = ' ' and uddldt = ldpldt)
or (ldtldg <> ' ' and ldtldg = ldpldt));
rtnAmt -= sqlTotAmt;



SQL0305 Diagnostic 30 28/09/17 07:30:26.345730 QSQRUN2
QSYS *STMT QSQRUN2 QSYS
From module . . . . . . . . :
QSQFETCH
From procedure . . . . . . :
CK_DEBUG
Statement . . . . . . . . . : 23851

To module . . . . . . . . . :
QSQFETCH
To procedure . . . . . . . :
CK_DEBUG
Statement . . . . . . . . . : 23851

Thread . . . . : 00000084

Message . . . . : Indicator
variable required.
Cause . . . . . : A FETCH, embedded
SELECT, CALL, GET DESCRIPTOR, or a SET
or VALUES INTO statement has resulted in a null value, but an indicator
variable was not specified for host variable SQLTOTAMT. The relative
position of the host variable in the INTO clause or parameter list is 3. If
the host variable name is *N, a descriptor area was specified. If this error
occurs on a GET DESCRIPTOR statement, the null value is being returned but
the INDICATOR item was not specified on the GET DESCRIPTOR statement.
Recovery . . . : Specify an
indicator variable, and precompile the
program again. If this is a GET DESCRIPTOR statement, specify both the DATA
item and the INDICATOR item.
Precompile the program again.

Appreciate any suggestions

Don Brown