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

Steve, that is correct. That is the situation we're trying to check. I
think the TO_NUMBER should be returning a NULL (the hyphen) which I
expected would be handled by the COALESCE.

Thanks,

Rob

On Fri, Apr 10, 2020 at 1:22 PM Steve M via MIDRANGE-L <
midrange-l@xxxxxxxxxxxxxxxxxx> wrote:

Robert,

I noticed the "iiupc" on the first row is mixed; numbers and an 'E'. The
TO_NUMBER column also appears to have a hyphen for that row. Could that be
your root cause?

Steve

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

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