Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 1281
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 sort records based on length exceeds ... maxsubrat DFSORT/ICETOOL 7 Wed Oct 04, 2017 4:48 pm
No new posts Partial color change of a field in CI... waseem0424 CICS 5 Fri Sep 29, 2017 7:56 pm
No new posts column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm
No new posts Remove leading spaces from numeric field rexx77 SYNCSORT 6 Wed Sep 06, 2017 2:15 am
No new posts JES2 job size field matching Windows ... SRICOBSAS All Other Mainframe Topics 4 Tue Sep 05, 2017 5:49 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us