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 ......
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With