AS400 rpgle embedded sql using sensitive cursor with over()

Question

Language: RPGLE

  exec sql
    declare    c1_RC0113_PLP04
    sensitive
    scroll
    cursor for
    select     ROW_NUMBER() over ( order by A4KQTT ) as rownbr,
               A4KQTT,
               A4SCAC,
               '0',
               ' '
    from       PLP04
    where      A4KQTT >= :#SCACDESC
    and        A4A8TA  = 'A'
    and        A4KQTT  > ' '
    order by   A4KQTT;

SQL Error:
 Message ID . . . . . . :   SQL0243       Severity . . . . . . . :   30        
 Message type . . . . . :   Diagnostic                                         
 Date sent  . . . . . . :   10/01/19      Time sent  . . . . . . :   16:55:33  

 Message . . . . :   SENSITIVE cursor C1_RC0113_PLP04 cannot be defined for the
   specified SELECT statement.                                                 
 Cause . . . . . :   The cursor C1_RC0113_PLP04 is defined as SENSITIVE but the
   query requires the creation of a temporary result table.  If the SELECT     
   statement has a data change table reference, it requires a temporary result 
   table.  A SENSITIVE cursor cannot be implemented.                           
 Recovery  . . . :   Redefine the cursor as ASENSITIVE or INSENSITIVE or change
   the query so that it no longer requires the creation of a temporary result  
   table.

I am assuming i getting the error because of the over()?

The reason I am using sensitive is I want to show inserts/updates to file if they happen.

Thanks in advance.

Mark Barinstein · Accepted Answer · 2019-10-02 05:04:15Z

1

Yes.
A cursor must be materialized to compute the result of such an OLAP function for each row. This is why you can't define it as sensitive.

answered Oct 2, 2019 at 5:04

Mark Barinstein

12.8k2 gold badges11 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

i03604m Over a year ago

Mark, i did remove the OLAP and then no error occurred on open but then I lose my functionality the OLAP gave me. Bottom line, for my program I need the OLAP functionality so I cannot make the cursor sensitive.

Mark Barinstein Over a year ago

You may probably enumerate the rows in a result set in your application without using OLAP functions, if your cursor is forward-only. If a cursor is not forward-only, then such an enumeration has no sense - you may have to re-enumerate the rows every time you scroll back and forth, and new rows appear / disappear in the cursor.

Collectives™ on Stack Overflow

AS400 rpgle embedded sql using sensitive cursor with over()

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related