Re: SQL help

Per the documentation for the function Count the result cannot be null,

"The result of the function is a large integer and it must be within the
range of large integers. The result *cannot* be null."

So should your case statement be more of select case when count(*) > 0 the
1 else 0 end into :wcount?

In these situations I have actually defined my host variable as an
indicator instead of an integer b/c it looks like you are ultimately
checking existence.

Now to the data decimal error. I ran a similar query where the join
resulted in no records and didn't have any errors. Is there any more
detail you can provide? I'm at a loss on why it would have a problem
setting wCount.

On Thu, Jul 1, 2021 at 8:47 AM Niels Liisberg <nli@xxxxxxxxxxxxxxxxx> wrote:

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

--
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