Re: SQL help

[Charles Wilt <charles.wilt@xxxxxxxxx>]

This is normally what you'd use to check for existence...

recExists = *off;
exec sql
  select '1' into :recExists
  from sysibm.sysdummy1
  where exists (select 1
                From file1 inner join file2
                    on date1=date2
                Where account = 123
                );
This takes advantage of the fact that if a record doesn't exist, then no
rows are returned (from sysdummy1) and the recExists variable is
unchanged.  You can also check for SQLSTATE=02000-->no data.

If you want a statement that will always return either '1' or '0'...then
something like so works (on 7.2 and higher at least)
exec SQL
  values coalesce((select '1' into :recExists
                     from sysibm.sysdummy1
                    where exists (select 1
                                  From file1 inner join file2
                                      on date1=date2
                                  Where account = 123
                                  )
                   ),'0') into :recExists;

Not sure how far back the above works...
Many moons ago (v5?), I tried to figure out such a statement, I was
successful, but as I recall it wasn't nearly as clean.  (for certain
definitions of clean I suppose :)


Charles


On Thu, Jul 1, 2021 at 7:22 AM <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