Re: Return count of records from SQL select

On 06-Apr-2018 14:29 -0600, Dan wrote:

On 06-Apr-2018 14:16 -0600, Dan wrote:

<snip> I need to know ONLY the number of rows that would be
returned from an SQL select statement. I don't need to know
anything about the rows themselves. The decision I need to make is
based on whether the number of rows from the select statement is
zero or greater than zero. <snip>

the select is:
Exec SQL select *
from File1, File2
where key1 = key2
and SUID1 like '%N1%'

If the actual count of rows is immaterial [i.e. if the needed info is truly limited to *only* the binary effect of whether there are "zero or greater than zero" rows meeting the criteria], then consider the following SQL requests, for which there would *not* be a requirement for the DB2 engine to perform either any counts or to find more than just _one qualifying row_, as contrasted with a request to perform a COUNT aggregate for which unnecessary work might delay the outcome for the required decision:



values(ifnull( (select char('1')
from file1 f1
where exists
(select '1'
from file2 f2
where f2.key2 = f1.key1
)
and suid1 like '%A1%'
fetch first 1 row only
)
, char('0')
)
)


values(ifnull( (select char('1')
from qsys2.qsqptabl
where exists
(select '1'
from file1 f1
join file2 f2
on f2.key2 = f1.key1
where suid1 like '%N1%'
)
)
, char('0')
)
)


And if the SET syntax is desired, then that would be similarly functional, solely by replacing the "VALUES" with "SET myVar ="; the above syntax is offered, so as to enable showing the effect in a SQL reporting session.