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

Thanks for the correction Rob. It seems like I'd had issues in the past. Sometimes the years run together. :-)

Steve Needles

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Rob Berendt
Sent: Monday, October 02, 2017 8:54 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: RE: SQL0305 Message in 5000 page joblog

I think SQL does a fine job of summing nulls.

create table qtemp.mytable (
mychar char(5),
mydec dec(15, 5));

insert into qtemp.mytable (mychar, mydec) values ('A', 2); insert into qtemp.mytable (mychar) values('B'); insert into qtemp.mytable (mychar, mydec) values ('C', 4);

select * from qtemp.mytable;
MYCHAR MYDEC
A 2.00000
B -
C 4.00000

select sum(mydec) as total from qtemp.mytable; TOTAL
6.00000

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com

From: Steve Needles <Stephen.Needles@xxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 10/02/2017 09:35 AM
Subject: RE: SQL0305 Message in 5000 page joblog
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>

Don,

I think that your math will fail as presented if any of the variables is
null. I don't know if it would result in an SQL0305 error or not.

You need to resolve each potentially null variable before being able to
use it in a SUM scalar function. I think that you will need something
like:

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

Otherwise you are attempting math on invalid values. Nulls aren't numbers
after all.

Steve Needles

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Mark Murphy
Sent: Saturday, September 30, 2017 3:35 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: 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

--
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

CONFIDENTIAL: The information contained in this email communication is
confidential information intended only for the use of the addressee.
Unauthorized use, disclosure or copying of this communication is strictly
prohibited and may be unlawful. If you have received this communication in
error, please notify us immediately by return email and destroy all copies
of this communication, including all attachments.
--
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

--
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

CONFIDENTIAL: The information contained in this email communication is confidential information intended only for the use of the addressee. Unauthorized use, disclosure or copying of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please notify us immediately by return email and destroy all copies of this communication, including all attachments.