|
View previous topic :: View next topic
|
| Author |
Message |
umashankar pathak
New User
Joined: 14 Jun 2009
Posts: 13
Location: Bangalore(INDIA)
|
|
|
|
Hi,
In my one of the program we are executing below SQL:-
| Code: |
SELECT L74.CUMM_OPEN_CNT
INTO :MFS-L74-A999.CUMM-OPEN-CNT:WS-L74-IND
FROM L74_A999 L74
WHERE L74.INT_PLAN_ID = :MFS-L74-A999.INT-PLAN-ID
AND L74.INT_VEH_NUM = :MFS-L74-A999.INT-VEH-NUM
AND L74.SUPERSHEET_DTE
= (SELECT MAX(L74A.SUPERSHEET_DTE)
FROM L74_A999 L74A
WHERE L74A.INT_PLAN_ID
= :MFS-L74-A999.INT-PLAN-ID
AND L74A.INT_VEH_NUM
= :MFS-L74-A999.INT-VEH-NUM) |
since this query we run against large volume of data thats why it is taking more CPU time and in order to reduce CPU time I have written the below SQL:-
| Code: |
SELECT L74.CUMM_OPEN_CNT
INTO :MFS-L74-A999.CUMM-OPEN-CNT:WS-L74-IND
FROM L74_A999 L74
WHERE L74.INT_PLAN_ID = :MFS-L74-A999.INT-PLAN-ID
AND L74.INT_VEH_NUM = :MFS-L74-A999.INT-VEH-NUM
ORDER BY L74.SUPERSHEET_DTE DESC
FETCH FIRST ROW ONLY |
=============================
I have tested both SQL's and got same result but some body told me like if you are using fetch first row with order by there may be a possibility to get wrong row.
Can some body tell me the facts about this like is really a scenario where my 2nd SQL(order by + fetch first row) will give the wrong row?
Regards,
Uma Shankar Pathak |
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello and welcome to the forum,
| Quote: |
| some body told me like if you are using fetch first row with order by there may be a possibility to get wrong row. |
You need to get them to explain why they believe this. To me it makes no sense. . . 
Actually, if the result might be multiple rows, the way to predict the proper sequence is using an order by. Otherwise the result can be unpredictable. Possibly you can post their doubt.
Suggest before posting more syntax,you practice using the "Code" tag to preserve alignment and improve readability. After you enter your post, click Preview to see how your post will appear to the forum rather than how it appears in the Reply Editor. When the post looks like you want, Submit. |
|
| Back to top |
|
|
umashankar pathak
New User
Joined: 14 Jun 2009
Posts: 13
Location: Bangalore(INDIA)
|
|
|
|
Thanks Dick for quick response.......
I have discussed with people who said fetch first row with order by some time returns a wrong row they believe this because for fixed pack 8.1 in DB2..
Please follow below link:-
www-01.ibm.com/support/docview.wss?uid=swg1IY68831
Is there any such kind of thing in mainframe also?
Once again Thanks for your help and support
Regards
Uma Shankar Pathak |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| Quote: |
have discussed with people who said fetch first row with order by some time returns a wrong row
|
Didn't they see that this fix has been done (UDB) ?
| Quote: |
I have tested both SQL's and got same result
|
Trust your instincts in this case. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Thanks Ashimer 
Uma Shankar Pathak,
You're welcome - i believe you are "good to go"
d |
|
| Back to top |
|
|
umashankar pathak
New User
Joined: 14 Jun 2009
Posts: 13
Location: Bangalore(INDIA)
|
|
|
|
Ok Thanks for clarification now I am going to install this in production.....
Regards,
Uma Shankar Pathak |
|
| Back to top |
|
|
GlobalGyan
New User
Joined: 31 Jan 2006
Posts: 28
|
|
|
|
I was wondering what is there are multiple values for CUMM_OPEN_CNT for a SUPERSHEET_DTE.
Would MAX and FETCH FIRST 1 ROW give the same result?
One suggestion, please do not share exact code on online forums. It looks bad :-)... and worse may violate rules of sharing internal restricted information. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| One suggestion, please do not share exact code on online forums. It looks bad :-)... |
Opinions vary. . .
There is nothing wrong (and it is often most helpful) with sharing tested code. The only caution is that proprietary information should not be posted. . .
Also, showing the exact code reduces the chance of mis-understanding.
As to the MAX question, suggest you run a test and post your finding. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| simple logic : what if max() = 2010-02-22 and there are two rows with dte = 2010-02-22 => select would return 2 rows |
|
| Back to top |
|
|
|
|
