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: 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 find RACF group for access to spooled... jzhardy JCL & VSAM 1 Mon May 08, 2017 11:46 am
No new posts READ A PACKED "NEGATIVE" FI... jdesouza CA Products 3 Tue May 02, 2017 11:43 pm
No new posts Group Data based on a key Arun Raj DFSORT/ICETOOL 7 Thu Apr 27, 2017 11:29 pm
No new posts Alter &DATENS field in HEADER1 Angad DFSORT/ICETOOL 4 Mon Apr 24, 2017 11:49 am
No new posts Need Help with : IFTHEN(WHEN=GROUP) Sumanta_89 DFSORT/ICETOOL 1 Wed Apr 19, 2017 5:23 pm


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