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
 

 

DB2 SQL fetch first row with order by clause

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
umashankar pathak

New User


Joined: 14 Jun 2009
Posts: 13
Location: Bangalore(INDIA)

PostPosted: Sun Feb 14, 2010 10:49 am    Post subject: DB2 SQL fetch first row with order by clause
Reply with quote

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

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sun Feb 14, 2010 11:40 am    Post subject:
Reply with quote

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. . . icon_confused.gif

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
View user's profile Send private message
umashankar pathak

New User


Joined: 14 Jun 2009
Posts: 13
Location: Bangalore(INDIA)

PostPosted: Mon Feb 15, 2010 7:34 pm    Post subject: Reply to: DB2 SQL fetch first row with order by clause
Reply with quote

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

http://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
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Mon Feb 15, 2010 7:44 pm    Post subject:
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Feb 15, 2010 10:28 pm    Post subject:
Reply with quote

Thanks Ashimer icon_smile.gif

Uma Shankar Pathak,

You're welcome - i believe you are "good to go" icon_smile.gif

d
Back to top
View user's profile Send private message
umashankar pathak

New User


Joined: 14 Jun 2009
Posts: 13
Location: Bangalore(INDIA)

PostPosted: Tue Feb 16, 2010 9:47 pm    Post subject:
Reply with quote

Ok Thanks for clarification now I am going to install this in production.....

Regards,
Uma Shankar Pathak
Back to top
View user's profile Send private message
GlobalGyan

New User


Joined: 31 Jan 2006
Posts: 28

PostPosted: Tue Feb 16, 2010 10:28 pm    Post subject:
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Feb 16, 2010 10:53 pm    Post subject:
Reply with quote

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

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon Feb 22, 2010 2:30 pm    Post subject:
Reply with quote

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
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
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
No new posts Order BY AND Index Nileshkul DB2 2 Sat Dec 31, 2016 6:33 pm
No new posts Multi row fetch - "for read only... Nileshkul DB2 3 Sun Aug 14, 2016 12:52 am
No new posts Need help on SQL Dynamic WHERE Clause subratarec DB2 12 Sat Jul 16, 2016 3:11 pm
No new posts JCL to fetch schedule status from act... parasmalik20 CA Products 1 Thu Jun 02, 2016 7:11 pm


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