Re: SQL0305 Message in 5000 page joblog

value + NULL = NULL

NULL is only "ignored" in aggregate functions such as SUM(), MIN(), MAX(),
AVG()...

Charles



On Tue, Oct 3, 2017 at 11:35 AM, Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>
wrote:

Hi Chris

I've not tested it - but do + and - still complete if one of their addends
is NULL? With a result of being NULL? If so, then this won't hurt, seems to
me.

Here is a statement from the manual about arithmetic operators, which
suggests that they do not stop the operation -

If any operand can be null, the result can be null. If any operand has the

null value, the result of the expression is the null value.

I also think that the OP said that all 5 values come from the same table.
Would they all be NULL or all not be NULL, then?

Regards
Vern


On 10/3/2017 9:21 AM, Hiebert, Chris wrote:

This may be a very "bad" fix because this will skip a row if *any* of the
values are null.

If 4 out of 5 of the fields contain a value, do you really want to omit
the row?

Or would you rather just zero out that single column?

That is the reason multiple people have already suggested this:

sum(coalesce(uddamt,0) + coalesce(uddaoa,0) +
coalesce(uddeam,0) - coalesce(uddcct,0) - coalesce(uddbkf,0))



Chris Hiebert
Senior Programmer/Analyst
Disclaimer: Any views or opinions presented are solely those of the
author and do not necessarily represent those of the company.
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Don Brown
Sent: Monday, October 2, 2017 4:06 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: SQL0305 Message in 5000 page joblog

Thank you to all who replied.

I have modified the sql as follows which has solved the issue - joblog
now down to 3 pages.

TotAmt = 0;
select COALESCE(sum(uddamt + uddaoa + uddeam - uddcct - uddbkf), 0) into
:TotAmt

Just to reply to a couple of questions raised;

All the fields in the sum( ) are from the same file and are numeric
fields so if a record is retrieved there will not be null values in any of
the summed fields.

The where clause did result in no records found quite commonly and
therefore the reason the SQL0305 was being received - no records found =
null result. But the TotAmt was initialised to zero so while the sql
statement resulted in a null result the return value was still zero.

And I have removed the sql prefix from the variable :-)

Cheers


Don Brown




From: "Sam_L" <lennon_s_j@xxxxxxxxxxx>
To: midrange-l@xxxxxxxxxxxx
Date: 03/10/2017 03:46 AM
Subject: Re: SQL0305 Message in 5000 page joblog
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



Don,

This suggestion from Steve appears to be what you should be doing:

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

From what I can tell, you are using 4 files and selecting just one
field, which is the sum over 5 fields. I can't tell from the names, but
I'd guess that the 5 fields come from at least two different files.

So, probably one (or more) of the joined files has no matching record and
the field from that file results in a null value. This null in turn
results in the sum returning a null.

Depending on your underlying logic, the program may currently be
returning incorrect results. I'd hazard a guess that if you change it to
use coalesce, you'll get different results from the original program using
the same data.

Sam

(Is the program checking SQLCOD or SQLSTATE after the select statement?
If not, it should.)


On 10/2/2017 8:35 AM, Steve Needles wrote:

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

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus


--
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 email has been scanned by the Symantec Email Security.cloud service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________


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