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
 
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: 1281
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 Moving values to a variable of copybo... Vignesh Sid COBOL Programming 6 Wed Sep 06, 2017 1:04 pm
No new posts Updating the Trailer count in variabl... satheshbabur DFSORT/ICETOOL 6 Wed Aug 30, 2017 9:49 pm
No new posts Move from Comp3 variable to Edited Va... sreekusr COBOL Programming 8 Thu Aug 10, 2017 4:20 pm
No new posts REXX - Dynamic file Creation d_sarlie CLIST & REXX 7 Tue Jun 27, 2017 7:30 pm
No new posts Dynamic output file creation in cobol... smileheal COBOL Programming 7 Thu Jun 15, 2017 10:53 pm

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