Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2013
    Posts
    6

    Question Unanswered: Working with nested cursor in Sybase

    I am trying to work with nested cursor, where value from the first cursor is passed on to the second.

    Below is the code snippet I used.

    declare compCur10 CURSOR FOR
    SELECT col1, col2 FROM tab1 where col3 = 28646
    go

    declare @var1 int

    declare prodCur6 CURSOR FOR
    SELECT col1, col2 FROM tab2 where col4 = @var1 /*this is the variable derived from cursor 1*/
    go

    declare @var2 int
    declare @var3 int
    declare @var4 int


    OPEN compCur10
    BEGIN
    FETCH compCur10 INTO @var1, @var2
    IF @var2 = 1
    BEGIN
    OPEN prodCur6
    BEGIN
    FETCH prodCur6 INTO @var3, @var4
    END
    CLOSE prodCur6
    END
    END
    END
    close compCur10


    The above query is throwing error 'DECLARE CURSOR must be the only statement in a query batch. '
    How do I pass value between cursors?

  2. #2
    Join Date
    Dec 2013
    Posts
    14
    Yeah, creating cursors in batched SQL is quite limited.

    You have a few different options:

    - put all of your code into a stored procedure; stored procs can have multiple cursor declarations, and those declarations can reference local @variables

    - write a single cursor that consists of a join of the 2 current cursor SELECT statements; I'm assuming you left out some code to simplify your example, eg, you have some other non-prodCur6 processing to do if @var2!=1 ... in which case you may need to write an outer join to make sure you bring back all tab1 rows regardless of a matching row in tab2

    - create a #table (in a separate batch before the cursor declarations) to store a single @var1 value; as you step through compCur10 you truncate #table and insert @var1; redefine prodCur6 to join tab2 with #table [yeah, a bit of a kludge]

    - if you've used application context functions (ACFs; set_appcontext(), get_appcontext(), rm_appcontext()) you could place @var1 into an ACF and then reference that ACF in the declaration of prodCur6; the general idea is to use the ACF as a global variable; [if you haven't used ACFs before then skip this suggestion as ACFs are a bit complicated to work with let alone maintain]

  3. #3
    Join Date
    Dec 2013
    Posts
    14
    To get around this limitation (no other commands other than declare/cursor in a SQL batch) you have a few options:

    1 - use a single cursor that is a join between tab1 and tab2 [based on your limited code sample it looks like you may need to write this as an outer join, eg, you need to process each row in tab1 but there may not be a matching row in tab2 ... ??]

    2 - put the code into a stored proc; stored procs allow for multiple cursor declarations as well as the referencing of local @variables within the cursor definition

    3 - plan on putting the value of @var1 into a #temp table (single column, single row), then join the #temp table with tab2 for your cursor declaration

    4 - if you've used application context functions (ACFs) before then you could plan on putting the value of @var1 into the ACF and reference the ACF (get_appcontext()) in place of @var1 in the cursor definition [ACFs are a bit complicated to setup and the associated coding can be tricky to design/maintain, so if you've not used ACFs before then ignore this suggestion]

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Maybe you don't need cursors, can you explain what you are trying to do with sample data and expected output from sample data.

  5. #5
    Join Date
    Dec 2013
    Posts
    6
    To explain the requirement -

    User will be prompted to enter a value, say '123', which my script will use to query table tab1. The resulant fields will be stored in a placeholder (here, I have used a cursor compCur10). Note, it can return multiple rows.

    Now, among these fields, col1 is a column having 2 possible values - 1 & 2.

    IF col1 = 1
    BEGIN
    --query table tab2 as -
    SELECT var1, var2, var3 from tab2 where var2 = col2 (col2 is a column from tab1)
    -- store the above result in a place holeder (for which I intend to use the second cursor)
    --print the results.

    ELSE
    -- query table tab3 as -
    SELECT y = SELECT var1 from tab3 where var2 = col2
    -- store the above result in a place holder (for which I intend to use the 3rd cursor)
    --print the results.

    Can anyone suggest an alternative approach?

    Now the limitations are - I do not have permission to create table/proc

  6. #6
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    I think you want
    Code:
    SELECT var1, var2, var3 
    FROM tab2 
    WHERE EXISTS 
    (SELECT 1 
     FROM tab1
     WHERE tab1.col1=1
       AND tab1.col2=tab2.var2)
    UNION ALL 
    SELECT var1, var2, var3 
    FROM tab3
    WHERE EXISTS 
    (SELECT 1 
     FROM tab1
     WHERE tab1.col1<>1
       AND tab1.col2=tab3.var2)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •