Paging in the stored procedure

Question

I want to make a custom paging according to the following link:

but my stored procedure is in informix:

so I search for the equivalent row_number() I find this solution

but when I use it in my procedure I get the following error:

SQL error -944 Cann't use "first","limit","skip" in this context !

How to rewrite the stored procedure in the previous link with informix rules?

my procedure has multiple unions is like this:

create procedure get_inbox_page(p_emp_num smallint, p_task_code smallint, p_main_code smallint default 0, p_year smallint default 0,p_page int)
returning varchar(60) as v_page_view, varchar(60) as v_task_name, smallint as v_task_code, varchar(60) as v_sp_name, varchar(60) as v_db_name, varchar(60) as v_column_key,  smallint as v_trans_serial, 
    DATETIME YEAR TO FRACTION(3) as v_trans_date, varchar(60) as v_trans_desc , varchar(60) as v_emp_name, varchar(60) as v_values_key, smallint as v_old_state_serial, 
    smallint as v_new_state_serial, smallint as v_main_code, smallint as v_year, varchar(60) as v_page_new, smallint as v_task_type, smallint as v_task_status,smallint as v_mail_maincode,smallint as v_mail_year,smallint as v_mail_number,smallint as v_trans_year , smallint as candidate_flag ;

Select  ......

UNION 

Select ......

UNION 

Select ......

score 2 · Answer 1 · answered Jul 02 '13 at 23:45

2

You cannot use FIRST, SKIP etc, because each SELECT statement within the UNION collection is self-contained.

Perhaps you could try creating a VIEW that encapsulates the SELECT ... UNION ... SELECT logic, and then apply the FIRST/SKIP logic to that.

answered Jul 02 '13 at 23:45

RET

9,100
1
28
33

Would `SELECT SKIP 100 FIRST 100 * FROM TABLE(MULTISET(SELECT ... UNION SELECT...)` work? – Conrad Frix Jul 10 '13 at 17:59

score 0 · Answer 2 · answered Jul 02 '13 at 11:19

try this

;
WITH    tempPagedRecord
          AS ( SELECT   Column1 ,
                        Column2 ,
                        Column3
               FROM     TableName AS e--Your query with union 
               UNION ALL
               SELECT   Column1 ,
                        Column2 ,
                        Column3
               FROM     TableName AS e--Your query with union
             )
    SELECT  * ,
            ROW_NUMBER() OVER ( ORDER BY Column1 ASC ) AS RowNumber--RowNumber over some column
    FROM    tempPagedRecord 
 WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1

This is not Informix syntax, which is what the OP requested. — RET, Jul 02 '13 at 23:39
The @ notation is not Informix; the OLAP functionality is in Informix 12.10, I believe. — Jonathan Leffler, Jul 05 '13 at 03:00

Paging in the stored procedure

2 Answers2