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
C/free
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
midrange.com
