Parameters example 2

These parameters can replace '?' in the query using 'STRING', as shown in Example 2a, 2b, and 2c:

  • request.WORK_DATE
  • request.START_DATE
  • request.END_DATE

Example 2a: Oracle

source='SELECT DEPT_ID, DEPT_NAME, DEPT_LOC_NAME,DEPT_LOC_DESC from
VL_DEPARTMENT WHERE TO_DATE(cast(? as char(17)), \'yyyymmdd hh24miss\')
<= DEPT_END_DATE and DEPT_START_DATE <= TO_DATE(cast(? as char(17)),
\'yyyymmdd hh24miss\') order by=DEPT_LOC_NAME '
sourceParams='STRING==#request.WORK_DATE#~|~STRING==#request.WORK_DATE#'
searchColumns='DEPT_LOC_NAME'

Example 2b: DB2

source='SELECT DEPT_ID, DEPT_NAME, DEPT_LOC_NAME,DEPT_LOC_DESC from
VL_DEPARTMENT WHERE Timestamp( CASE WHEN cast(? AS varchar(17)) = \'\'
THEN NULL ELSE cast(? AS varchar(17)) END ) <= DEPT_END_DATE and
DEPT_START_DATE <= Timestamp( CASE WHEN cast(? AS varchar(17)) = \'\' THEN NULL ELSE cast(? AS varchar(17)) END ) '
sourceParams='STRING==#request.WORK_DATE#~|~STRING==#request.WORK_DATE#'
orderBy='DEPT_LOC_NAME' searchColumns='DEPT_LOC_NAME'

Example 2c: SQL Server

p. source='SELECT DEPT_ID, DEPT_NAME, DEPT_LOC_NAME,DEPT_LOC_DESC from
VL_DEPARTMENT WHERE CONVERT( DATETIME, LEFT( ?, 8 ), 112 ) <=
DEPT_END_DATE and DEPT_START_DATE <= CONVERT( DATETIME, LEFT( ?, 8 ),
112 )'
sourceParams='STRING==#request.WORK_DATE#~|~STRING==#request.WORK_DATE#'
orderBy='DEPT_LOC_NAME' searchColumns='DEPT_LOC_NAME'