存储过程中的分页
提问于 2013-07-02 02:45:14
我想根据下面的链接进行自定义分页
但是我的存储过程在informix中：
所以我搜索等价的row_number()，我找到了这个解决方案
但是，当我在我的过程中使用它时，我会得到以下错误：
SQL错误-944不能在这个上下文中使用“第一”、“限制”、“跳过”！
如何用informix规则重写前面链接中的存储过程？
我的程序有多个联盟是这样的：
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 ......
回答 2
Stack Overflow用户
发布于 2013-07-02 15:45:14
您不能使用FIRST、SKIP等，因为UNION集合中的每个SELECT语句都是自包含的。
也许您可以尝试创建一个封装了VIEW逻辑的SELECT ... UNION ... SELECT，然后将FIRST/SKIP逻辑应用于此。
Stack Overflow用户
发布于 2013-07-02 03:19:52
尝尝这个
;
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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/17423546
复制相关文章
点击加载更多
腾讯云开发者
