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
 

 

Dynamic SQL for Fixed list result but variable WHERE clause.

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

New User


Joined: 29 Feb 2008
Posts: 49
Location: Pune

PostPosted: Tue Feb 15, 2011 1:38 pm    Post subject: Dynamic SQL for Fixed list result but variable WHERE clause.
Reply with quote

I need to code a dynamic SQL which will fetch a fixed number of columns (so fixed list), but the where clause may vary on the basis or requirement.

What I found in Redbook is this:
Code:
EXEC SQL DECLARE C1 CURSOR FOR STMT;
MOVE 'SELECT NAME, PHONE, ADDRESS, STATE FROM TABLE1 WHERE PHONE = ?' TO DSTRING
EXEC SQL PREPARE STMT FROM :DSTRING;
EXEC SQL OPEN C1 USING :PARM1;
EXEC SQL FETCH C1 INTO :NAME, :PHONE, :ADDRESS, :STATE;


But here I believe the constrain is PARM1 should match with 1st field in the where clause.... & so on...

My requirement is:
I should be able to build the where clause dynamically. For example:
Case 1. Need to pass PHONE in WHERE clause.
Case 2. Need to pass NAME in WHERE clause.

Could you help me in coding this.
Or shall I refer to the VARYING-LIST Dynamic SQL.[/code]
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Feb 15, 2011 2:10 pm    Post subject:
Reply with quote

Code:
if blabla
   MOVE 'SELECT NAME, PHONE, ADDRESS, STATE FROM TABLE1 WHERE PHONE = ?' TO DSTRING
   EXEC SQL PREPARE STMT FROM :DSTRING;
   EXEC SQL OPEN C1 USING :PARM-phone;
else
   MOVE 'SELECT NAME, PHONE, ADDRESS, STATE FROM TABLE1 WHERE NAME = ?' TO DSTRING
   EXEC SQL PREPARE STMT FROM :DSTRING;
   EXEC SQL OPEN C1 USING :parm-name;
end-if
edited
Back to top
View user's profile Send private message
grayWolf

New User


Joined: 04 Oct 2010
Posts: 19
Location: Land of broken dreams

PostPosted: Thu Feb 17, 2011 12:59 pm    Post subject:
Reply with quote

Considering a dynamic query, a better approach would be using something like this:

In the working storage declare as below

Code:
01  WS-QUERY1.
  05 WS-QUERY1A.
     10 PIC X(08) VALUE ' SELECT '.
     10 PIC X(5) VALUE   ' NAME,'.
     10 PIC X(06) VALUE   ' PHONE,'.
     10 PIC X(10) VALUE   ' ADDRESS,'.
     10 PIC X(5) VALUE   ' STATE ,'.
     10 PIC X(12) VALUE   ' FROM TABLE1'.
  05 WS-WHERE PIC X(30) VALUE SPACES.
  05 WS-QUERY1B PIC X(24)  VALUE ' FOR FETCH ONLY WITH UR'.


In the WS-WHERE variable, move whatever "WHERE" clause is required.
(This approach is best suited when multiple conditions have to checked. If you have only 2 cases follow the other approach)

MOVE ws-query1 to DSTRING
Prepare the statement and open the dynamic cursor as given by Guy


Let us know if it worked.


Regards,
Back to top
View user's profile Send private message
abdul.faras

New User


Joined: 29 Feb 2008
Posts: 49
Location: Pune

PostPosted: Fri Feb 18, 2011 1:07 pm    Post subject:
Reply with quote

Thanks GuyC and Wolf..
Will try both..
icon_biggrin.gif
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 REXX DB2: Dynamic allocation of DB2.D... BHAS CLIST & REXX 3 Mon Dec 19, 2016 8:26 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts Cobol list of programs being called biswajit.dattagupta COBOL Programming 5 Tue Nov 01, 2016 2:10 am
This topic is locked: you cannot edit posts or make replies. Rexx to create GDG by scanning JJ result krish.deepu CLIST & REXX 5 Tue Oct 25, 2016 5:32 pm
No new posts Format to pipe delimited with variabl... pshongal SYNCSORT 6 Wed Sep 14, 2016 2:48 pm


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