PeopleSoft-页面报表多条件搜索功能

第一种

1
2
3
4
5
6
7
8
9
10
Local Rowset &rs = GetRowset(Scroll.C_SAL_QRY_VW);

REM 清空rowset;
&rs.Flush();
rem 根据输入的查询条件进行查询;
&rs.Select(Record.C_SAL_QRY_VW, &WHERE);

rem 排序;
&rs.Sort(C_SAL_QRY_VW.TREE_NODE_NUM, "A", C_SAL_QRY_VW.EMPLID, "A");

第二种

1
2
3
4
5
6
7
8
9

ScrollFlush(Record.C_PRH_SER_VW);

&strWhere = "WHERE 1 = 1 ";
rem 默认出待入职的;
&strWhere = &strWhere | " AND C_PRH_STATUS = '10' ";
&strWhere = &strWhere | " AND OPRID = '" | %OperatorId | "'";

ScrollSelect(1, Record.C_PRH_SER_VW, Record.C_PRH_SER_VW, &strWhere);

注意

在使用Rowset的Fill方法时,需要注意,不要为Rowset定义别名,否则会查不出数据的;

捕获错误

原表中有datetime类型,使用视图SQL时;原表中的datetime可以正常在视图中使用;

但是,如果该视图在页面中被作为Rowset使用,那么使用Rowset的select /fill/scrollselect 等(即第一种方式);

搜索时,会默认转换为char(string类型),而页面中如果直接用视图中的datetime字段,会导致页面报错 sql error;

如下:

1
2
3
4
5
PSAPPSRV.9263 (2123) [2021-02-08T18:00:34.030 SIE_ZL@192.168.0.252 (CHROME 88.0.4324.150; WIN10) ICPanel] XJFyb8kCIOQsQA 767851693135939585 SIE_ZL (3) File: /vob/peopletools/src/psppr/ramget.cppSQL error. Stmt #: 709 Error Position: 0 Return: 1841 - ORA-01841: (full) year must be between -4713 and +9999, and not be 0 

Failed SQL stmt:

SELECT C_APPLI_NUMB, TO_CHAR(C_EFFDT,'YYYY-MM-DD'), C_APPLI_LEVEL, EMPLID, SETID, C_WORK_CHK_SYS_ID, C_SEQ5, C_REST_DAY, C_IS_WEEKEND, C_IS_LEGAL_HOLI, COMMENTS, C_VACATION_DF, C_VACATION_MORE, ATTACHSYSFILENAME, ATTACHUSERFILE, CST_IMPTFILE_FLAG, C_APPLI_RS, C_SECTION_TYPE, EMPLID2, C_OA_APPL_NUM, TO_CHAR(CAST((LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), LASTUPDOPRID FROM PS_C_VACATIO_TBL_V WHERE 1 = 1 ORDER BY C_APPLI_NUMB

解决方式:在视图中,用string类型字段替换datetime类型字段;或者,页面中datetime类型字段用string类型字段替换;