In many projects, the scenario is that you need to find if the rows exist or not for a particular condition.

Performance is the main issue if you write complex SQL queries. The best and latest approach is to use FETCH FIRST 1 ROW ONLY. When you specify it in your SQL query, the result will be 1 or more than one row. Our aim is to find whether the rows exist or not.

The sample SQL query below helps you see a real use of the above phrase.

FETCH FIRST 1 ROW ONLY is as efficient as a correlated subquery, and it is a more straightforward coding style.

Fetch First 1 Row Only:

In the below query, when a single row is found, you will get SQLCODE as zero. So that you can set the flag as rows found to true.

MOVE ZEROS TO HOST-VARIABLE1
SELECT  1
INTO    :HOST-VARIABLE1
FROM    TABLE1
WHERE   COLUMN1    =  :HOST-VARIABLE-X
  AND   COLUMN2    =  :HOST-VARIABLE-Y
FETCH FIRST 1 ROW ONLY

EVALUATE SQLCODE …‥

IF SQLCODE = 0
    SET  ROWS-FOUND   TO TRUE
END-IF