Dynamic Cursor DB2

Ok, here is the question.

I need define a cursor having select statement with dynamic where clause.

Here is the detailed requirement in points.

1.      Create a stored procedure with parameters which will be part of where clause of cursor in procedure definition/body.
2.      This SP will have the cursor a select statement with dynamic where clause.
3.      This stored procedure will be executed from a .bat file with parameters.


Please let me know how do I do this in DB2.

Sample code would be of great help
LVL 14
ajexpertAsked:
Who is Participating?
 
ajexpertAuthor Commented:
I request EE volunters to close the question as I have found the solution
Here its explained in breif
*******************************************************************************************
DECLARE v_v_static_sql2  VARCHAR(1000);
 DECLARE v_stmt             STATEMENT;
       
DECLARE at_end                      SMALLINT DEFAULT 0;
 SET v_static_sql2 = '
          SELECT
        n.CLIENT_ID AS "CLIENT ID",
        c.CORP_ID                ,
        a.ADDRESS_ID,
        n.NAME_BUSINESS AS "NAME BUSINESS",
        a.ADDRESS1,
        a.HOUSE_NBR "HOUSE",
        a.ADDRESS2,
        a.ADDRESS3,
        a.ADDRESS4,
        a.CITY,
        a.STATE_PROV_CD "STATE",
        a.POSTAL_CODE "POSTAL CODE"
        FROM
              CLI_CLIENT c
            JOIN CLI_CLNT_NAME_V n ON c.CLIENT_ID = n.CLIENT_ID
            LEFT OUTER JOIN CLI_CLNT_ADDR_V ca ON n.CLIENT_ID = ca.CLIENT_ID
            LEFT OUTER JOIN CLI_ADDRESS_V a ON ca.ADDRESS_ID = a.ADDRESS_ID
          WHERE
            n.SEQ_NBR = 1
            AND c.SEGMENTED_FLAG = ''N''
            AND n.NAME_BUSINESS IS NOT NULL ';

        IF   v_nonsel_sql IS NULL then
            SET v_nonsel_sql = ' ';

  PREPARE v_stmt FROM v_sql;
 OPEN cur_dupclnt;
            fetch_loop:
            REPEAT

            FETCH cur_dupclnt INTO
<your logic here?
            UNTIL at_end > 0
            END REPEAT fetch_loop;
  CLOSE cur_dupclnt;
0
 
momi_sabagCommented:
hi
i did not understand what exactly do you need when you say dynamic where clause
do you need
1) a where clause with unknown predicates, that is sometimes it will have where col1 = 1 and sometimes it will have where col2 = 1
2) a where clause with dynamic parameters, that is sometimes where col1 = 1 and sometimes where col1 = 2

thanks
0
 
momi_sabagCommented:
plus
do you want it to be a sql procedure or a procedure using some other langauage such as java
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
ajexpertAuthor Commented:
Ok,
Here i rephrase the query.

I want a cursor for which a select statement can be build on run time.
This is very similar to REF CURSOR in ORACLE

Like there will be a SELECT statement with WHERE CLAUSE and I have to process this statement via CURSOR LOOP

I have searched something like PREPARE STAMENT, EXEC SQL should do the trick, but I am unable to compile in DB2 command center.

I hope my question is clear, If not let me know I will explain with example
0
 
momi_sabagCommented:
post here the code you cant compile and the message you ger and i'll try to tell you whats wrong with it
0
 
ajexpertAuthor Commented:
OK...I found the solution...

I request EE Volunters to please close the question.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.