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.

score 1 · Answer 1 · answered Oct 02 '19 at 05:04

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 02 '19 at 05:04

Mark Barinstein

11,456
2
8
16

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. – i03604m Oct 02 '19 at 18:21
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. – Mark Barinstein Oct 02 '19 at 18:56

AS400 rpgle embedded sql using sensitive cursor with over()

1 Answers1