Re: SQL0305 Message in 5000 page joblog

I generally wrap the offending statement in a coalesce() function as you
mentioned. This removes the need to look for a null indicator value later.

If you go the null indicator route, you have to put some code in to inspect
that indicator and do something if a null was returned.

On Sat, Sep 30, 2017 at 9:17 AM, Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>
wrote:

Hi Don

This message does mean that there was no result from some statement, so
the assignment of a value did not happen, hence, the result has an unknown
result, otherwise known as NULL attribute.

In this circumstance, I'm inclined to recommend using a null indicator -
this is easy enough to do.

COALESCE can also do it, so give it a try if you want - this might take a
little more code than a NULL indicator, haven't tested - but the indicator
might be a touch less. Probably no difference that matters.

Certainly the message takes a lot more processing, and clogs up the job
log, AND the result value mightn't change from any previous successful
value assignment. Again, I've not tested that possible side effect.

Since you reset SQLTOTAMT, the last matter is not a problem.

BTW - do NOT use variable names that start with SQL - those are reserved
for things that the SQL engine uses. TOTAMTSQL would be fine - and
personally I don't see a reason to use SQL in a name, since that's not
really its functional purpose in most cases.

Regards
Vern


On 9/29/2017 10:10 PM, Don Brown wrote:

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

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD