For -811 condition which row retrived

poul_motiram · New User Joined: 05 Dec 2006 Posts: 13 Location: chennai

Hi,

Can anybody help me in finding the answers to the follwing questions

If more than one row is retrieved using singleton SELECT, what is the value present in INTO clause HOST variable of SELECT, when SQLCODE is -811 .

plz refer this table:
EMPTABLE

ENO ENAME SAL
1 A 2000
2 B 6000
3 C 5000
4 D 4000

QUERY:

EXEC SQL
SELECT ENO ENAME SAL INTO :ENO :ENAME :SAL FROM EMPTABLE
END-EXEC.

for this query what is the value of host variables ?

Regards,
Motiram

shrinivas_3 · New User Joined: 05 Sep 2006 Posts: 34

ravi17s · New User Joined: 15 Aug 2003 Posts: 57

Yes thats Correct.
First qualifying row.

poul_motiram · New User Joined: 05 Dec 2006 Posts: 13 Location: chennai

kvivek · New User Joined: 09 May 2005 Posts: 51 Location: Singapore

Yes it is first qualifying row and it may not be the same as first row.

Select statement maynot retreive the rows in the same order.

Correct me if I am wrong.

Regards,
Vivek

poul_motiram · New User Joined: 05 Dec 2006 Posts: 13 Location: chennai

kvivek · New User Joined: 09 May 2005 Posts: 51 Location: Singapore

Select statement may retreive the rows in any order, not necessarily in the inserted order.

shrinivas_3 · New User Joined: 05 Sep 2006 Posts: 34

shrinivas_3 · New User Joined: 05 Sep 2006 Posts: 34

prakash271082 · New User Joined: 09 Sep 2005 Posts: 53

As said well by Shrinivas, make use of check ( IF SQLCODE = 0 ) so that you would be clear enough....

humpty · New User Joined: 01 May 2005 Posts: 23

dear,

in your case the data is in sorted manner, so first row will be selected.

in case you give the orderby clause, then the first qualifying row will be selected. in case you do not give any order by and the data is also not sorted, then in this case a temp table is built with data in the sorted manner and then the first row of this temp sorted table is fetched.

clear ??

cheers !!