|
View previous topic :: View next topic
|
| Author |
Message |
abdul.faras
New User
Joined: 29 Feb 2008
Posts: 49
Location: Pune
|
|
|
|
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 |
|
 |
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| 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 |
|
|
grayWolf
New User
Joined: 04 Oct 2010
Posts: 19
Location: Land of broken dreams
|
|
|
|
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 |
|
|
abdul.faras
New User
Joined: 29 Feb 2008
Posts: 49
Location: Pune
|
|
|
|
Thanks GuyC and Wolf..
Will try both..
 |
|
| Back to top |
|
|
|
|
