Re: COALESCE not working as I expected

[Carel <coteijgeler@xxxxxxxxx>]

Is the problem nor the naming of the column in the result set as 
"coalesce"?Then put it between double quotes to be accepted as a column 
name.

Kind regards,

Carel Teijgeler


Op 10-4-2020 om 18:48 schreef Robert Rogerson:
> 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