On 24 Apr 2012 07:47, Dave wrote:

I want to set the value of an indicator to *on if I find at least
one record matching my search criteria and *off otherwise.

What's the best way? eg:

indicator = *off;
exec sql
Select '1' into : indicator
from mytable
where -- ....err.. what goes here?
-- what if there are many rows returned?

The EXISTS predicate evaluates to true as soon as the first match is found, so there is no more processing required. There could be many thousands more matches or none, but no further evaluation is required after finding one, regardless how many more may or may not exist.

FWiW: To accomplish what was stated originally, as contrasted with the example which initializes the indicator, because the SQL treats the indicator data type as a CHAR(1), the following are some examples of how the SQL can be persuaded to set the value of the indicator named RowExists; either being set to *ON when at least one matching row is-found or being set to *OFF when at least one matching row is-not-found:

D isR S n

Exec SQL
select digits( dec( count(*), 1 ) )
-- dec( count(*), 1 )
-- dec( count('1'), 1 )
-- ifnull( cast( sum(1) as numeric(1) ) , '0' )
-- ifnull( dec( sum(1), 1 ) , '0' )
into :RowExists
from qsqptabl /* sysibm/sysdummy1 */
where exists ( select '1' from MyTable
where FldX=:xVal
and FldZ=:zVal )
// check here for bad sqlcode\sqlstate
If RowExists then; // SQL set this to *ON|*OFF if no
// error other than row-not-found

The casting was overt\explicit to ensure no default effects are assumed; e.g. numeric to character cast default behavior had changed in other DB2 variant(s). The commented lines are just alternatives means to accomplish the same; e.g. the first commented just drops the DIGITS casting which would be the effect from implicit casting into the CHAR(1) indicator, and the single-digit SUMs optionally could have been within a DIGITS() for a more obvious match to the constant\literal '0' as the second argument of the IFNULL().

Of course while that usage implies no requirement to initialize the indicator before the SQL SELECT, and similarly no requirement to check for the SQL0100 [or the Row Not Found state], that does not enable avoiding checking return codes from the SQL for any other possible problems with the statement; e.g. authority, existences of a table reference, et al.

Because the sql return codes would still need to be checked, there is likely little value from coding something like that. That coding would probably just be confusing to most; i.e. they, expecting instead the selection of a literal and the check for row-not-found or worse to follow the statement. So the example is offered as an example only, to be sure, *not* as a recommendation.

Regards, Chuck

This thread ...

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].