- SQL code
DROP PROCEDURE "JDE_REPORT"."INV_04A"GOCREATE PROCEDURE "JDE_REPORT"."INV_04A" (IN P01_DBNAME CHARACTER VARYING(7), IN P02_WORKSHOPPRODUCTION CHARACTER VARYING(10), IN P03_BEGINDATE_FROM DATE, IN P04_BEGINDATE_TO DATE, IN P05_USER CHARACTER VARYING(10)) LANGUAGE SQL SPECIFIC JDE_REPORT.INV_O4A DYNAMIC RESULT SETS 1 MODIFIES SQL DATA DISALLOW DEBUG MODE OLD SAVEPOINT LEVEL COMMIT ON RETURN NOBEGIN DECLARE CSR CURSOR WITH RETURN FOR Select * From CrpDta.f563111 Fetch First 100 Rows ONly; OPEN CSR;END --call JDE_REPORT.INV_04A('PY812','','1/14/2009','1/14/2010','200')
执行以上存储过程,得到了100行数据.现在请问如何给结果集加上一个列标识,1至100这样的,自动增长。请问该如何编写?
------解决方案--------------------------------------------------------
Select row_number() over() as rn, CrpDta.f563111.* From CrpDta.f563111 Fetch First 100 Rows ONly;
这样试一下
------解决方案--------------------------------------------------------
把这句
Select * From CrpDta.f563111 Fetch First 100 Rows ONly;
改为
select * from
(
Select row_number() over(order by 主键) as num,CrpDta.f563111.* From CrpDta.f563111
) as x
where num between 1 and 100