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
 
Altering ORDER BY fields based on a condition

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

New User


Joined: 13 Apr 2007
Posts: 36
Location: Pune

PostPosted: Tue Jul 29, 2008 1:24 pm    Post subject: Altering ORDER BY fields based on a condition
Reply with quote

Hi,
I have a requirement which consists of a cursor with certain 'order by' fields wherein, depending on the value of a field that lies in the middle of the sequence, the fields that follow that field in the 'order by' list will also change.
To show it with the example, the order by clause looks like this:
ORDER BY CTRY
, BNK
, CURR
, ACCT
, (CASE REC_TYPE
WHEN '001' THEN 1
WHEN '002' THEN 2
WHEN '003' THEN 3
WHEN '004' THEN 4
WHEN '006' THEN 5
WHEN '021' THEN 6
WHEN '022' THEN 7
WHEN '010' THEN 8
WHEN '013' THEN 9 END) ASC
, CUST
, AS_DT
, TYPE_CD
, AMT

Now the requirement is something like this.
While doing an order by, if the REC_TYPE value comes out to be 010,
then the rest of order by should happen on all the four fields that follow
the parantheses, i.e CUST, then by AS_DT, then by TYPE_CD, then by AMT.
However if the record is having any other record type, then the rest of the order by should not contain the fields which are in bold above.
i,e if the REC_TYPE is having a value 006, then the ORDER BY should happen ONLY on AS_DT and then on AMT.

Is this really doable? I was able to find some ORDER BY OF ORDER XXX type of explanations, but not sure if that will fit here.

Another aspect of this is that this ORDER BY clause is a part of the cursor which is a part of a stored proc and just as you open the cursor, all the values get displayed on an interface. There is no FETCH involved.

Please help.

Thanks,

Ashu
Back to top
View user's profile Send private message

ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Jul 29, 2008 2:36 pm    Post subject:
Reply with quote

Order by Order of is feature of Db29 ...and it is not used for the purpose which you have mentioned ...Ashu it is not mandatory that fetch has to be there in the SP ..it should be there in the calling program which could be from front end or from back end itself ... normally SP's just open the cursor and returns back the control to the caller ....

i dont think your requirement is doable in an SQL ...or else you may have to use arrays and do a sort based on rec_types ....
Back to top
View user's profile Send private message
ashutosh.pr

New User


Joined: 13 Apr 2007
Posts: 36
Location: Pune

PostPosted: Thu Jul 31, 2008 10:33 am    Post subject: Reply to: Altering ORDER BY fields based on a condition
Reply with quote

Thanks Ashimer,
I guess we cannot get this in SQL. The problem is that the cursor when opened sends the result set on to AQT ( Advanced Query tool). I havent got much chance to find out if doing it with arrays will be an option here.

Thanks
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: Thu Jul 31, 2008 7:26 pm    Post subject:
Reply with quote

Hello,

Depending on how intense the SELECTs and how large the found set, you might also consider creating a temporary table from the found set that is the same for all variations and then define the cursor on the temporary table. Your processing logic would be the same for all cases, but the selection would be customized for each situation.

You might also consider having multiple cursors and using the appropriate cursor for each execution. Not my favorite, but . . .
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 BUILD OUTFIL based on condition other... balaji81_k DFSORT/ICETOOL 13 Fri Sep 08, 2017 11:06 pm
No new posts Sorting on text - but in non-alphabet... Roy Ware SYNCSORT 5 Wed Aug 23, 2017 9:15 pm
No new posts How do you create Eclipse based dialogs jasorn IBM Tools 0 Thu Aug 03, 2017 5:05 pm
No new posts Split file based on comparsion to 2 f... Div Grad DFSORT/ICETOOL 3 Thu Jul 27, 2017 9:16 pm
No new posts How to write Specific Fields from Mul... Padhu SYNCSORT 6 Thu Jul 06, 2017 10:26 am

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