How can I code a Select statement in Natural DB2 in which I would like to pick up only one row or the first record that is retrieved?
It’s SELECT SINGLE or alternatively SELECT … OPTIMIZE FOR N ROWS
thanks for the reply but it didn’t work. i got the message “-811 THE RESULT OF AN EMBEDDED SELECT STATEMENT OR A SUBSELECT IN THE SET CLAUSE OF AN UPDATE STATEMENT IS A TABLE OF MORE THAN ONE ROW, OR THE RESULT OF A SUBQUERY OF A BASIC PREDICATE IS MORE THAN ONE VALUE.” OPTIMIZE, meanwhile, didn’t pass the syntax checker.
my code looks like this:
SELECT SINGLE
PR.A, PR.B,
PR.C, PR.D,
PR.E
INTO
PROF.A, PROF-A.B,
PROF-A.C, PROF-A.D,
PROF-A.E
FROM
PROFILE PR
WHERE
PR.A = ‘AA’ AND
PR.F = #F AND
PR.G = #G AND
PR.H IN (‘G’, ‘O’, ‘C’) AND
PR.I IS NOT NULL
WITH HOLD
PERFORM PROCESS-1
END-SELECT
anything i missed?
It’s been a few years since I was at a Natural DB2 shop but if I recall correctly
SELECT SINGLE must be guaranteed unique and if more than 1 record results you get -811 so this should only be used if there can only be one record resulting.
OPTIMIZE FOR 1 ROWS should work. Are you saying ROWS or ROW? I believe it should always be plural.
This from the online documention on SAG:
The OPTIMIZE FOR integer ROWS clause is used to inform DB2 in advance of the number (integer) of rows to be retrieved from the result table. Without this clause, DB2 assumes that all rows of the result table are to be retrieved and optimizes accordingly.
This optional clause is useful if you know how many rows are likely to be selected, because optimizing for integer rows can improve performance if the number of rows actually selected does not exceed the integer value (which can be in the range from 0 to 2147483647).
The clause you need is “FETCH FIRST ROW ONLY”