|
View previous topic :: View next topic
|
| Author |
Message |
razesh84
New User
Joined: 05 Apr 2010
Posts: 41
Location: Kolkata,India
|
|
|
|
can anyone please tell me if a singleton query returns more than a row then which row(record) will be stored in the host variable.
with my tests everytime i got the second record in the host variable irrespective of no. of record satisfies the condition
lets say the below query[ignore the INTO stmnt] returns 4 rows with seq_nbr 1,2,3,4 in spufi in that order
& when i embedded the sql in a cobol code it always gives seq_nbr- 2
And i didnt give any order by clause
| Code: |
SELECT seq_nbr
INTO :ws-seq-nbr
FROM emp1
WHERE emp_id = 1 |
Initially i thought that it may depends on some default installation values & may get varied from shop to shop.
but the same thing happens in my new project also & no one is aware of this.
Need a clarification. |
|
| Back to top |
|
 |
don.leahy
Active Member
Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada
|
|
|
|
The row returned in this situation is, by definition, unpredictable. It is possible that in a future release, DB2 may return no row at all.
The proper way to handle this is to code your program as if no row was returned.
If you are relying on -811 as an "existence check" there are many better ways to achieve this.
If this is a case of wanting one row returned, but you don't care which one, then use a CURSOR. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| can anyone please tell me if a singleton query returns more than a row then which row(record) will be stored in the host variable. |
Only one row will be returned if the sqlcode is zero. Before attempting to use the host variable(s) value(s), the sqlcode must be tested.
If the sqlcode of the SELECT is non-zero, do not use the value(s) in the host variables.
Keep in mind that your code must follow the rules or the way the code works may change when there is a ptf applied or an upgrade is done. |
|
| Back to top |
|
|
razesh84
New User
Joined: 05 Apr 2010
Posts: 41
Location: Kolkata,India
|
|
|
|
Hi,
In some of our production code sqlcode 0 & -811 were handled together hoping that in case of multiple rows satisfies the condition the first row will be fetched in the host variable.
| Code: |
EVALUATE SQLCODE
WHEN ZERO
WHEN -811
MOVE TABLE-DATA TO WS-TABLE-DATA
WHEN OTHER
.........
END-EVALUATE
|
One of such code requires some tuning & when i added FETCH FIRST ROW there the o/p files didnt match.After buring few ATPs I found that conditions where multiple rows satisfied were unmatched.Previoulsy it was picking the 2nd record now the first one.
I asked this question to every senior members of my team & got so many different answers[first row,junk,last row,dont know,unpredictable] that made me think it may vary on some parameter & in our shop it definately returns 2nd row.Now the same thing happens in my new project.
So does it happen everwhere?can you guys do a little test & post the result here.Curious to know!!!!
@don
| Quote: |
If you are relying on -811 as an "existence check" there are many better ways to achieve this.
If this is a case of wanting one row returned, but you don't care which one, then use a CURSOR.
|
its not for existence checking.even if cursor was used are you telling me to fetch the cursor single time?may be FETCH FIRST ROW serve the situation better.
@dick
| Quote: |
If the sqlcode of the SELECT is non-zero, do not use the value(s) in the host variables |
thanks for the suggestion |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
as of db2 vsn 7, a -811 will not guarrentee population of the result set - there may or may not be some data there.
prior to vsn 7 (that means vsn 6 or less) a -811 would populate the result.
with vsn 7 came FETCH FIRST ROW ONLY.
with vsn 8 you can ORDER BY and FETCH FIRST ROW ONLY.
razesh84,
my suggestion to you is after your experience with your senior people (who obviously do not know) is to read the manual. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| In some of our production code sqlcode 0 & -811 were handled together hoping that in case of multiple rows satisfies the condition the first row will be fetched in the host variable. |
Suggest you get rid of any/all code that is based on hope. . .
Code must be written to work all of the time because it is correctly coded - not that the result "hoped for". . . There is no end to the damage that can be caused implementing this way. |
|
| Back to top |
|
|
|
|
