Re: SQL0305 Message in 5000 page joblog

[Sam_L <lennon_s_j@xxxxxxxxxxx>]

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