Re: Embedded sql how to test for no lines

[Jonathan Ball <jonball52@xxxxxxxxx>]

It is incorrect that this

   select char(count(1))
   into :existsFlag
   from <your table>
   where <your conditions>
   limit 1;

will return only 0 or 1. It will return a number equal to the number of rows in 
the table that match the selection criteria. SELECT COUNT() *always* returns 
only one row, so LIMIT 1 has no effect on this.

I usually do this:

   dcl-s exists char(1) inz('N');

   [...]

   exists = 'N';  // if the test has been run already

   exec sql
   select 'Y'
     into :exists
     from <my table>
    where <my conditions>
    limit 1;

   If exists = 'Y';
      <do some stuff>
   end-if;

If it's a large table, you'll definitely want an index over the table with keys 
matching the columns tested in <your conditions>.


On 8/27/2024 11:31 AM, Daniel Gross wrote:
> Hi Brian,
>
> your missing the from clause:
>
>    select '1'
>    into :existsFlag
>    from sysdummy1
>    where exists (select ...);
>
> But I think you can get it even faster and easier:
>
>    select char(count(1))
>    into :existsFlag
>    from <your table>
>    where <your conditions>
>    limit 1;
>
> This way you don't have to observe sqlcode=100 - because count() never emits 100. As the select is limited to 1 row, you can only get values 1 and 0 - and those are converted to char - and then to IND.
>
> This is the tactic I use very often in my programs.
>
> HTH
> Daniel
>
>
> > Am 27.08.2024 um 19:18 schrieb Brian Garland via RPG400-L <rpg400-l@xxxxxxxxxxxxxxxxxx>:
> >
> > If memory serves me correctly EXISTS is more efficient than COUNT.
> >
> > DCL-S ExistsFlag IND;
> > ExistsFlag = '0'
> > SELECT '1' INTO :ExistsFlag WHERE EXISTS(SELECT......)
> >
> > Brian