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
 

 

How to make the order by clause as dynamic

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> COBOL Programming
View previous topic :: :: View next topic  
Author Message
birdy K

New User


Joined: 05 Mar 2008
Posts: 72
Location: chennai

PostPosted: Wed Sep 17, 2008 4:04 pm    Post subject: How to make the order by clause as dynamic
Reply with quote

Hi all,

How to make the order by clause as dynamic. That is I have to pass the column name or column no before opening the cursor. Is any other way to do this? Suggestions are welcome. Thank You
Back to top
View user's profile Send private message

acevedo

Active User


Joined: 11 May 2005
Posts: 344
Location: Spain

PostPosted: Wed Sep 17, 2008 5:12 pm    Post subject: something like?
Reply with quote

Code:

EXEC SQL                                               
   DECLARE <CURSORNAME> CURSOR FOR                     
                                                       
   SELECT CASE                                         
           WHEN :W-column = 'column2' THEN COLUMN2     
           WHEN :W-column = 'column3' THEN COLUMN3     
           ELSE                            COLUMN1     
           END                                         
         ,<othercolumns>                               
     FROM <yourtablehere>                               
    WHERE <conditions>                                 
    ORDER BY 1                                         
END-EXEC                                               
Back to top
View user's profile Send private message
birdy K

New User


Joined: 05 Mar 2008
Posts: 72
Location: chennai

PostPosted: Wed Sep 17, 2008 5:48 pm    Post subject:
Reply with quote

Hi acevedo,

You have given Order by 1. I am asking how to pass the "1" and the format desc or asc dynamically before the open cursor statement. Order by 1 is hard coded. My requirement is to have a single cursor and the column no and format is to be changed as per the requirement from java team. Java team is calling that SP. They will give me the format(desc or asc) and column no. So I have to move that before open cursor statement.
Back to top
View user's profile Send private message
acevedo

Active User


Joined: 11 May 2005
Posts: 344
Location: Spain

PostPosted: Wed Sep 17, 2008 6:04 pm    Post subject:
Reply with quote

birdy K wrote:
Order by 1 is hard coded.


CASE do the trick, the field to order by is always the first.

other option, go with Dynamic SQL.
Code:

EXEC SQL                                                   
   DECLARE C1     CURSOR FOR STMT                         
END-EXEC                                                   
EXEC SQL                                                   
   PREPARE STMT INTO :MINSQLDA FROM :W-STATEMENT           
END-EXEC                                                   


where w-statement contains the SQL.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Wed Sep 17, 2008 9:19 pm    Post subject:
Reply with quote

Quote:

SELECT CASE
WHEN :W-column = 'column2' THEN COLUMN2
WHEN :W-column = 'column3' THEN COLUMN3
ELSE COLUMN1
END


Here the datatypes of all the columns used in the CASE stmt has to be of the same type ....
Back to top
View user's profile Send private message
acevedo

Active User


Joined: 11 May 2005
Posts: 344
Location: Spain

PostPosted: Thu Sep 18, 2008 1:40 am    Post subject:
Reply with quote

that's a snippet...I'm not at work but you need to CAST the numeric columns or DATES/Timestamps...to char.
Back to top
View user's profile Send private message
birdy K

New User


Joined: 05 Mar 2008
Posts: 72
Location: chennai

PostPosted: Thu Sep 18, 2008 2:13 pm    Post subject:
Reply with quote

Thank You All
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 -> COBOL Programming All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Dynamic split of files under groups sril.krishy DFSORT/ICETOOL 4 Mon Apr 17, 2017 1:09 pm
No new posts Dynamic array request/response contai... Suja.Sai CICS 2 Tue Jan 24, 2017 11:37 am
No new posts Order BY AND Index Nileshkul DB2 2 Sat Dec 31, 2016 6:33 pm
No new posts REXX DB2: Dynamic allocation of DB2.D... BHAS CLIST & REXX 3 Mon Dec 19, 2016 8:26 pm
No new posts Single COPY CICS TS datasets and when... Kyle Carroll CICS 2 Tue Oct 11, 2016 9:47 pm


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