Fetch first 1 Row only Real Usage in DB2

In many projects, the scenario where 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 using 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 if the rows exist or not.

The below Sample SQL query helps you real use of above phrase.

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

Fetch First 1 Row Only:

In the below query, when single row is found, you will get SQL CODE as zeros. So that you can set 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

Related Posts