I was under the impression a SELECT statement will fail if more than one row matches the selection criteria. Yet, I have someone who swears this works, although I have not seen any evidence to support this contention. It's a very simple SELECT, coming from a WebSphere SQL:

SELECT ACCT_NBR
FROM HOFF.T09_ACCOUNTS
WHERE CUST_ID = W_CUST_ID

I have no way of running this myself, except to code it in a COBOL program on the mainframe. I'm not sure that would prove anything.

whenever there is more than 1 row - a cursor has to be declared and fetch has to be executed on cursor. otherwise a direct exec sql select into :hv ... can be used

Please explain "direct exec sql select into :hv ". My DB2 programming skills are fairly basic. Does a dynamic SQL sent through DB2 connect return all rows of a result set at once?

If you can run it via SPUFI, you can run it via DB2 Connect.

A query that returns a single row is fetched directly (no client side cursor is instantiated). If more than one row is returned, then the client instantiates a cursor and each row is fetched and returned. It appears to everything higher in the client's protocol stack like the entire result set is returned at once.

-PatP

So, no matter what, I need a cursor somewhere. That's what I figured. Thanks.

have a look at application guide or cobol samples shipped how todo this

If you need only one row, you can get it using singleton select:

Lenny

For a singleton select, if multiple rows are fetched for the condition in the where predicate, it will throw -811 and that's why we go for CURSOR in DB2 and process each fetched row.

Hope this helps!!

- prevents sqlcode = -811

Kara

Now, that I recall!

From Performace point of view instead of "fetch first row only", people do not prefer writing a select query. Instead it is always recommended to do an "existence check".
Select 1
into :ws-xxxx
from xxxxx
where ??? = :?????

This always works out faster!!!

I'm sorry, but you don't understand how DB2 works.

Even with yours "existence check" you can get -811.

Kara

I have never said that "existence check" doesn't give -811. Did I? What solely I meant was instead of Select * or singleton select, it is always recommended to use "Existence check" when you just need to know whether the value is present in the table or not. Off course at the end of the SQL, you need to have ---

EVALUATE SQLCODE
WHEN +100
????????????????
WHEN -811
??????????
WHEN 0
?????????????
WHEN OTHER
??????
END-EVALUATE.

Could be better: