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



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.

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.