Re: SQL help

[Niels Liisberg <nli@xxxxxxxxxxxxxxxxx>]

Why does

exec sql *select ifnull(count(*)  , 0) into :counter from xxx ; *

.. not work for you?

else perhaps wrap it into a cte;

exec sql with mycount as (
   *select count(*) as cnt  from xxx *
)
select ifnull(cnt) int :counter from mycounter;




On Thu, Jul 1, 2021 at 3:22 PM <dfreinkel@xxxxxxxxxxxxxxxxx> wrote:

> I have a SQLRPGLE program with the following example
>
>
>
> Select CASE when count(*) is null then 0 else 1 end into :wCount
>
> From file1 inner join file2 on date1=date2
>
> Where account = 123
>
>
>
> I get a decimal data error and it appears that the problem is with the
> result, I assume. wCount is an integer. There are 2 records in the files
> being read.
>
>
>
> As it is in a RPG program, I cannot use IFNULL.
>
>
>
> I can run the statement in interactive SQL without a problem.
>
>
>
> Does anyone have a suggestion on how to resolve this?
>
>
>
> Thank you.
>
>
>
> Darryl Freinkel
>
> A4G
>
> Telephone: 770.321.8562 Mobile: 678.355.8562
>
>
>
> --
> This is the RPG programming on IBM i (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/rpg400-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