Re: COALESCE not working as I expected -- MIDRANGE-L

Thanks Rob. I almost understand what you're saying. But to be sure if I
try

SELECT mychar, to_number(mychar) as mynum
FROM (VALUES('30a')) X (mychar);
MYCHAR MYNUM
30a -

I see the error (in my case SQL0420). The hyphen is not really a null but
a representation of an error?

Then I ran

SELECT TO_NUMBER(mychar) as mynum
FROM (VALUES(' 10'),('20 '), ('30a')) X (mychar);
MYNUM
10
20
0

Again I see the error but this time it returns zero. I don't understand
why zero and not a hyphen as the previous example.
Lastly I tried

SELECT SUM(TO_NUMBER(mychar)) as mysum
FROM (VALUES(' 10'),('20 '), ('30A')) X (mychar);
MYSUM
-

I was expecting I would see a result of 30 (10 + 20 + 0).

As you explained the COAESCE doesn't work because TO_NUMBER is not really
returning a null when an error occurs.

I just don't understand why the results differ.

Thanks for all your time and research. Most likely I'll take your advice
and write a UDF to resolve my issue.

Thanks,

Rob

On Mon, Apr 13, 2020 at 8:07 AM Rob Berendt <rob@xxxxxxxxx> wrote:

The reason it is returning a null instead of doing the coalesce is that
the function errors out.

If you try this using VALUES instead of a SELECT such as this:
values to_number(0045E1020040), coalesce(to_number(0045E1020040), 0);
It will error out with
SQL State: 42820 Vendor Code: -410 Message: [SQL0410] Floating point
literal 0045E1020040 not valid. Cause . . . . . : The number of
characters in the floating point constant 0045E1020040 cannot exceed 42
excluding leading zeros. The number of digits in the first number cannot
exceed 34 excluding leading zeros, and the number of digits in the second
number cannot exceed 4. Recovery . . . : Correct the indicated literal
0045E1020040. Make certain that the floating point literal is in the
correct form shown by the following examples: +1.2E+3, 15E1, 2.E5, 2.2e-1,
+5.E+2, -.1e1. Try the request again.
It will not move on to the coalesce.

However, when you use a select it will use nulls and move on to the next
row. So, when it hits
coalesce(to_number(0045E1020040), 0)
It does not break this down into two distinct operations. It does not
break this down into
to_number(0045E1020040), 0)
then coalesce(null, 0)
It just returns the error and then converts it to a null.
So instead of coalesce(null, 0) you are seeing the results of
coalesce(error, 0). Coalesce does not know how to handle an error so it
returns a null.

And, no, you cannot nest these to fix it. IOW you cannot resolve it by
select to_number(mychar), coalesce(to_number(mychar), 0),
coalesce(coalesce(to_number(mychar), 0), 0)
from qtemp.rr;
as you will just end up with 3 nulls.

Any possibility that you can start to embrace null values instead of
fighting to replace them with some other default value, such as zero?

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

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Robert Rogerson
Sent: Friday, April 10, 2020 12:49 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: COALESCE not working as I expected

CAUTION: This email originated from outside of the organization. Do not
click links or open attachments unless you recognize the sender and know
the content is safe.

Hi all,

I have the following sql statement

SELECT ihponb, iiupc, TO_NUMBER(iiupc) AS to_number,
COALESCE(TO_NUMBER(iiupc), 0) AS coalesce, iiqtsh
FROM amcdta.ivrcvhp
JOIN amcdta.ivrcvdpbad
ON iivndn = ihvndn
AND iiivnb = ihivnb
WHERE ihponb = '103446'

which results in

IHPONB IIUPC TO_NUMBER COALESCE IIQTSH
103446 0045E1020040 - - 2989.400
103446 4531021916 4531021916 4531021916 7196.800
103446 4531021936 4531021936 4531021936 5667.200
103446 0 0 2730.400
103446 004531028057 4531028057 4531028057 1600.000
103446 004531028126 4531028126 4531028126 1600.000
103446 004531028147 4531028147 4531028147 1760.000
103446 004531028516 4531028516 4531028516 2639.700
103446 004531029600 4531029600 4531029600 2791.300

I've checked the manual (V7R3) and COALESCE states

"The arguments are evaluated in the order in which they are specified, and
the
result of the function is the first argument that is not null. The result
can be null
only if all arguments can be null, and the result is null only if all
arguments are
null."

So I don't understand why the result in the COALESCE column in row 1 is not
zero as zero is being specified as the second parameter in the
COAESCE function.

Can anyone enlighten me as I'm stumped.

Thanks,

Rob
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com