Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2003
    Posts
    30

    Unanswered: Declaring a Cursor with a order by clause

    Can we declare a cursor with an order by pharse in the select clause

    Declare cur_a1 cursor
    for select name, sal from emp
    where dept=10
    order by name asc
    for update of sal;

    Can we use this statement.
    If not how do we go about accomplshing the same result.
    The order in which the cursor will go thru the active set is very important.

    Thanks for your help.
    -Soumil

  2. #2
    Join Date
    Jul 2003
    Location
    China
    Posts
    9

    Re: Declaring a Cursor with a order by clause

    Here is an declaration of a cursor in my procedure
    " DECLARE TRADE_CURSOR CURSOR FOR SELECT DEBIT_AMOUNT,CREDIT_AMOUNT,ROW_ID FROM FM_ACCOUNT_DETAIL
    WHERE ACCOUNT_ID=IN_ACCOUNT_ID AND DATE(BOOK_DATE)=IN_SPEC_DATE AND ACTIVE_FLAG='1' ORDER BY BOOK_DATE;
    "

    it work!

    Originally posted by soumil
    Can we declare a cursor with an order by pharse in the select clause

    Declare cur_a1 cursor
    for select name, sal from emp
    where dept=10
    order by name asc
    for update of sal;

    Can we use this statement.
    If not how do we go about accomplshing the same result.
    The order in which the cursor will go thru the active set is very important.

    Thanks for your help.
    -Soumil

  3. #3
    Join Date
    Jul 2003
    Posts
    30

    Re: Declaring a Cursor with a order by clause

    Originally posted by rijkaard
    Here is an declaration of a cursor in my procedure
    " DECLARE TRADE_CURSOR CURSOR FOR SELECT DEBIT_AMOUNT,CREDIT_AMOUNT,ROW_ID FROM FM_ACCOUNT_DETAIL
    WHERE ACCOUNT_ID=IN_ACCOUNT_ID AND DATE(BOOK_DATE)=IN_SPEC_DATE AND ACTIVE_FLAG='1' ORDER BY BOOK_DATE;
    "

    it work!
    How do I make order by clause work with for update clause.
    The declaration of Cursor has to have both of these clauses.
    When I include the order by clause it give me the following error message :

    "SQL0511N The FOR UPDATE clause is not allowed because the table specified by the cursor cannot be modified. LINE NUMBER=97. SQLSTATE=42829
    "

    Thanks
    -Soumil

  4. #4
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106

    Re: Declaring a Cursor with a order by clause

    Whenever there is an ORDER BY, DISTINCT, GROUP BY specified as part of the SELECT in a cursor, DB2 automatically makes the result set read-only (ignoring a FOR UPDATE if that is specified as well).
    You will have to DECLARE the cursor with the SELECT ORDER BY and issue an UPDATE separately. Or you can change the DECLARE to a FOR statement:

    for cur_a1 as select name, sal, [need to specify primary key column(s)] from emp
    where dept=10
    order by name asc
    do
    update emp set sal = (something) where (primary key) = cur_a1.(primary key);
    end for;


    Originally posted by soumil
    How do I make order by clause work with for update clause.
    The declaration of Cursor has to have both of these clauses.
    When I include the order by clause it give me the following error message :

    "SQL0511N The FOR UPDATE clause is not allowed because the table specified by the cursor cannot be modified. LINE NUMBER=97. SQLSTATE=42829
    "

    Thanks
    -Soumil

  5. #5
    Join Date
    Nov 2006
    Posts
    19
    How would the syntax be different when you are going through an ODBC client? our developers are trying to do this with Visual Studio 2008 and can't get it to work.

    Thanks.

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by soumil
    Declare cur_a1 cursor
    for select name, sal from emp
    where dept=10
    order by name asc
    for update of sal
    The combination "order by" & "for update" are conceptually not compatible because DB2 cannot always guarantee an order without sorting and thereby materializing, which looses the connection with the original data (and hence is no longer updatable).

    Only when the "order by" can be done without "sorting", i.e., when it's done through an index, this combination is conceptually compatible.

    So the question becomes: how to force an index use without the "order by"?

    A possible approach could be the following, but verify the index use through EXPLAIN:

    1) create an index on (dept, name), in that order.
    2) rewrite the cursor declaration as
    Code:
    Declare cur_a1 cursor for
     select name, sal from emp
     where  dept=10
       and  name BETWEEN :hv1 AND :hv2
     for update of sal
    3) set host variables hv1 and hv2 to x'00' and x'FF' respectively, just before opening the cursor. (Where I'm assuming that all names are alphabetically between those two values on the host system. Change when necessary.)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  7. #7
    Join Date
    Nov 2006
    Posts
    19
    Thank you. Do you know why the sql interface doesn't like the 'declare'? Our developer is getting this:
    static scroll cursor for select * from test order by oi for update;
    ------------------------------------------------------------------------------
    exec sql declare c5 sensitive static scroll cursor for select * from test order by oi for update DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
    SQL0199N The use of the reserved word "DECLARE" following "" is not valid.
    Expected tokens may include: "IS <HEXSTRING> <CHARSTRING> <GRAPHSTRING> ".
    SQLSTATE=42601

    SQL0199N The use of the reserved word "DECLARE" following "" is not valid. Expected tokens may include: "IS <HEXSTRING> <CHARSTRING> <GRAPHSTRING> ".

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Since neither you nor your developer seem to be familiar with the concept of reading manuals, I have done it for you. The manual indicates that DECLARE CURSOR "is not an executable statement and cannot be dynamically prepared".
    ---
    "It does not work" is not a valid problem statement.

  9. #9
    Join Date
    Nov 2006
    Posts
    19
    Actually, yes he did, just don't know how to get around this. Thanks for the rudeness. It seems to be the 'thing' on this forum.

  10. #10
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by brdholman
    Actually, yes he did, just don't know how to get around this.
    Actually, that's not what you asked. Your question was "why".

    As to "how", your best option would be to incorporate the logic into a stored procedure and call it via ODBC.

    Quote Originally Posted by brdholman
    Thanks for the rudeness. It seems to be the 'thing' on this forum.
    I'm sorry if I offended you; I was trying to be sarcastic, but not rude. The 'thing' on this forum is that people are not obliged to answer questions, especially RTFM-able ones, and one can expect certain informality. If you require white glove treatment, try calling IBM support.
    ---
    "It does not work" is not a valid problem statement.

  11. #11
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by n_i
    If you require white glove treatment, try calling IBM support.
    I suppose this one was not sarcastic ;-)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  12. #12
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I was getting caught up some reading since the server was down for a while and thought I'd share a couple of thoughts.

    First, some of the comments above were great.

    Next, and I'm really surprised this didn't come up before. Why would you put an ORDER BY clause on this cursor???? Why not run the cursor and use UPDATE WHERE CURRENT OF........???????????????? The computer/database/and the application/and least of all the user/developer should not care one IOTA about which order the records get updated.

    Dave Nance

Posting Permissions

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