Re: SQL0305 Message in 5000 page joblog -- MIDRANGE-L

Hi Justin

You raise the matter of what NULL means.

In database, it is an __attribute__ - not really a value, although even IBM uses the incorrect phrase, null value.

NULL is not zero. If the value you are setting was zero when you ran the SQL that was setting it, and there was no return value, it is null - and the value you had is unchanged.

The OP was setting the value to zero before, so this would cause no trouble, when nothing found in the SELECT.

But it's better to either use a null indicator after resetting the variable - then you can check the indicator (or not) - but COALESCE gives you the ability to specify a value if the result is not available or know - the meaning of NULL.

Cheers
Vern

On 10/2/2017 11:22 AM, Justin Taylor wrote:

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