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 doubt - Sorting by non group by field

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

New User


Joined: 21 Mar 2007
Posts: 46
Location: India

PostPosted: Fri Mar 19, 2010 8:29 am    Post subject: DB2 SQL doubt - Sorting by non group by field
Reply with quote

Hi

Below is the DCLGEN of a table :-

Code:
PAR                        SMALLINT NOT NULL,     
BR                          INTEGER NOT NULL,       
ACC                        INTEGER NOT NULL,       
RUN_DATE               DATE NOT NULL,         
A_U_IND                 CHAR(1) NOT NULL,       
DRATE                    CHAR(4) NOT NULL,       
START_DA              DATE NOT NULL,         
ITYPE                     CHAR(1) NOT NULL,       
BLIMIT                   DECIMAL(13, 2) NOT NULL,
THOLD                   DECIMAL(15, 0) NOT NULL,
AIT                        DECIMAL(15, 0) NOT NULL

In the above table the keys are the column 1 - 6 ( i.e. from PAR till DRATE both inclusive).

Below is a query to return some rows from the table

Code:
   SELECT                                             
               A_U_IND                         
               , MIN(RUN_DATE)                             
               , DRATE                                 
               , THOLD                         
         FROM                                               
               TABLE1                           
         WHERE                                               
             PAR = :TABLE1.PAR
   AND       BR = :TABLE1.BR
   AND       ACC = :TABLE1.ACC               
   AND       RUN_DATE BETWEEN :WS-START-DATE AND :WS-END-DATE
   AND       ITYPE IN ('A' 'B' 'T')           
   GROUP BY A_U_IND,DRATE,THOLD             
     ORDER BY THOLD ASC   


In the above query I want to keep everything unchanged but just ensure that the results come in the sorted ascending order of RUN_DATE as well ( in addition to THOLD) . I am trying to put in ORDER by RUN_DATE as well but I am getting a SQL code of -122 . Is there any way this query can be redisgned to provide the same result set with the additional condition of being sorted by RUN_DATE as well.

Any help would be greatly appreciated.


2) Can any one tell me what is the SORT criterial in the above query? I see a trend of the result set being sorted on the basisi of A_U_IND ( when the THOLD are same). Is DB2 engine doing some other sort criteria on its own ? Can we play around with that SORT criteria to get our desired criteria of SORT on basis of RUN_DATE.?
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1280
Location: Belgium

PostPosted: Fri Mar 19, 2010 4:53 pm    Post subject:
Reply with quote

This is basic sql knowledge:
order by thold,min(run_date)
or
order by 4,2

If you don't specify order by or only one column (thold) the order of the rows is undefined and depends on the accesspath which can change on any rebind.
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 Why TS/OP would Not contact site supp... RahulG31 All Other Mainframe Topics 2 Wed Mar 22, 2017 7:46 am
No new posts SORT VSAM file with each field one by... maxsubrat DFSORT/ICETOOL 6 Tue Mar 14, 2017 1:07 pm
No new posts IMS DB-How to update a record (a sing... Nic Clouston IMS DB/DC 9 Thu Mar 09, 2017 4:38 pm
No new posts outrec field outside range Danielle.Filteau SYNCSORT 10 Sat Mar 04, 2017 2:37 am
No new posts 2 byte YEAR from Y'DATE2' field tecnokrat DFSORT/ICETOOL 2 Tue Feb 07, 2017 12:05 pm


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