× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.