Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

For -811 condition which row retrived

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
poul_motiram
Currently Banned

New User


Joined: 05 Dec 2006
Posts: 13
Location: chennai

PostPosted: Mon Dec 18, 2006 2:23 pm    Post subject: For -811 condition which row retrived
Reply with quote

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
Back to top
View user's profile Send private message

shrinivas_3
Warnings : 1

New User


Joined: 05 Sep 2006
Posts: 34

PostPosted: Mon Dec 18, 2006 5:02 pm    Post subject: Re: For -811 condition which row retrived
Reply with quote

poul_motiram wrote:
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 .






Hi

As per my understanding the host variable will have 'THE FIRST QUALIFIED ROW '

So in your case you should get
ENO ENAME SAL
1 A 2000

in the host variables.

correct me if i am wrong

Thanks
Shrinivas D
Back to top
View user's profile Send private message
ravi17s
Warnings : 1

New User


Joined: 15 Aug 2003
Posts: 57

PostPosted: Wed Dec 20, 2006 6:29 pm    Post subject: Re: For -811 condition which row retrived
Reply with quote

Yes thats Correct.
First qualifying row.
Back to top
View user's profile Send private message
poul_motiram
Currently Banned

New User


Joined: 05 Dec 2006
Posts: 13
Location: chennai

PostPosted: Thu Dec 21, 2006 9:00 am    Post subject: Re: For -811 condition which row retrived
Reply with quote

ravi17s wrote:
Yes thats Correct.
First qualifying row.




Hi,

I read it It retrives First qualifying Row Therotically.......

But in my notes (practically) It shows Second Row
i.e. the confusion for me.

If anyone know practically....
Please Correct me.

Regards,
Mots
Back to top
View user's profile Send private message
kvivek

New User


Joined: 09 May 2005
Posts: 51
Location: Singapore

PostPosted: Thu Dec 21, 2006 9:31 am    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
poul_motiram
Currently Banned

New User


Joined: 05 Dec 2006
Posts: 13
Location: chennai

PostPosted: Thu Dec 21, 2006 10:41 am    Post subject:
Reply with quote

kvivek wrote:
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



Hi,

But which row value it contain?

First, Second, Last or any (or combination)
Back to top
View user's profile Send private message
kvivek

New User


Joined: 09 May 2005
Posts: 51
Location: Singapore

PostPosted: Thu Dec 21, 2006 10:57 am    Post subject:
Reply with quote

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


Quote:
But which row value it contain?

First, Second, Last or any (or combination)


Answer for the above question is "unpredicted".


Regards,
Vivek
Back to top
View user's profile Send private message
shrinivas_3
Warnings : 1

New User


Joined: 05 Sep 2006
Posts: 34

PostPosted: Thu Dec 21, 2006 10:57 am    Post subject: Re: For -811 condition which row retrived
Reply with quote

poul_motiram wrote:
ravi17s wrote:
Yes thats Correct.
First qualifying row.




Hi,

I read it It retrives First qualifying Row Therotically.......

But in my notes (practically) It shows Second Row
i.e. the confusion for me.

If anyone know practically....
Please Correct me.

Regards,
Mots








Hi Mots

You had the below data setup

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

Now as per my guessing what might have happened is :

In the below query

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

Internally 1 A 2000 got populated in corresponding Host Variables first time .
(here SQLcode will be = 0 )


Since there are multiple qualifying Rows for the above query it might have fetched the second row with value 2 B 6000 into host variables OVERRIDING THE PREVIOUS VALUES (i.e 1 A 2000 ) Now the SQLCODE = -811

So it might be showing the second row in host variables

Try out this

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

If sqlcode = 0 or ?811
Move values of Host variables into corresponding working storage variables
Display corresponding working storage variables
Else
Abend
End-if.

THis is my guessing
Check out and let me know if it works.

THanks

Shrinivas D
Back to top
View user's profile Send private message
shrinivas_3
Warnings : 1

New User


Joined: 05 Sep 2006
Posts: 34

PostPosted: Thu Dec 21, 2006 11:08 am    Post subject:
Reply with quote

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


Quote:
But which row value it contain?

First, Second, Last or any (or combination)


Answer for the above question is "unpredicted".


Regards,
Vivek



Hi

Run the same query with same data setup SEQUENCE in SPUFI or QMF
and see the result .
THis will give you an IDEA about the ROW SEQUENCE FETCHED

Thanks

Shrinivas D
Back to top
View user's profile Send private message
prakash271082

New User


Joined: 09 Sep 2005
Posts: 53

PostPosted: Thu Dec 21, 2006 1:15 pm    Post subject:
Reply with quote

As said well by Shrinivas, make use of check ( IF SQLCODE = 0 ) so that you would be clear enough....
Back to top
View user's profile Send private message
humpty
Warnings : 1

New User


Joined: 01 May 2005
Posts: 23

PostPosted: Wed Jan 03, 2007 3:05 pm    Post subject: Re: For -811 condition which row retrived
Reply with quote

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 !!
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. How to pass the previous month date i... Suganya87 DFSORT/ICETOOL 5 Mon Oct 31, 2016 4:13 pm
No new posts INCLUDE condition questions van bui DFSORT/ICETOOL 1 Thu Jun 30, 2016 9:52 am
No new posts 2 vsam file compare and update a fiel... ABINAYATHULASI DFSORT/ICETOOL 6 Sun May 29, 2016 11:15 am
No new posts File matching using specific condition mf_karthik DFSORT/ICETOOL 3 Fri Nov 20, 2015 11:04 pm
No new posts INCLUDE condition for 'Contains' krsenthil85 DFSORT/ICETOOL 4 Mon Oct 26, 2015 11:03 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us