When a sql returns more than one value, what value will be stored in the host variable

Question

In COBOLDB2 program, what value will be stored in the host variable after getting -811 sqlcode. (i.e multiple rows returned by the query).

Answer 1

No data will be fetched to your host variable because SQLCODE < 0 means there is an error. Please refer to this link: IBM SQL Tutorial

You can use statements of this kind to retrieve single rows of data into host variables. The single row can have as many columns as desired. If a query produces more than one row of data, the database server cannot return any data. It returns an error code instead.

Answer 2

Indeed, no data can be fetched if you just use a query like that. In this case, you can use a CURSOR and the FETCH statement. This way, you can read into the host variable the returned lines one by one. In short, this goes like this:

declare cursor curs_name for select .... from....where .....
open curs_name
fetch curs_name into host_var
close curs_name 

All these instructions are enclosed between EXEC SQL ........... END EXEC. Of course, you have to fetch once for each line. You can check the SQLCODE to see if you reached the end of the cursor. You look for SQLCODE 100.