[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Dynamic Cursor DB2

Posted on 2007-10-05
6
Medium Priority
?
4,440 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:ajexpert
  • 3
  • 3
6 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 20027098
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 20027100
plus
do you want it to be a sql procedure or a procedure using some other langauage such as java
0
 
LVL 14

Author Comment

by:ajexpert
ID: 20027838
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 37

Expert Comment

by:momi_sabag
ID: 20027848
post here the code you cant compile and the message you ger and i'll try to tell you whats wrong with it
0
 
LVL 14

Author Comment

by:ajexpert
ID: 20029229
OK...I found the solution...

I request EE Volunters to please close the question.
0
 
LVL 14

Accepted Solution

by:
ajexpert earned 0 total points
ID: 21216195
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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses
Course of the Month17 days, 23 hours left to enroll

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question