I want to compare 2 date/timestamp fields. One of those comes from a table on Oracle, second one comes from a mapping variable for which a value is define in a parameter file. The datatype of the variable is defined as date/timestamp in the mapping. A filter makes the comparison for which the condition is:
DATE_COMPARE(LAST_UPD, $$CDC_STRT_TS) >=0 AND DATE_COMPARE(LAST_UPD, $$CDC_END_TS) < 0
However, on executing the session, I receive:
TE_7002 [<<PM Parse Error>> missing operator
... DATE_COMPARE(LAST_UPD, 02/01/2014>>>> <<<<01:01:01.000000000) >=0 AND DATE_COMPARE(LAST_UPD, 03/01/2014 01:01:01.000000000) < 0]
It appears that Informatica is putting a space when replacing the variable with its value. If I convert it to a string and use date conversion functions then Informatica encloses the variable in single quotes and does not replace it with the value defined in the parameter file (notice the where clause).
ORA-00942: table or view does not exist
Oracle Fatal Error
Database driver error...
Function Name : Execute
SQL Stmt : SELECT CX_NET_TER_HIER.ROW_ID, CX_NET_TER_HIER.CREATED, CX_NET_TER_HIER.CREATED_BY, CX_NET_TER_HIER.LAST_UPD, CX_NET_TER_HIER.LAST_UPD_BY, CX_NET_TER_HIER.MODIFICATION_NUM, CX_NET_TER_HIER.CONFLICT_ID, CX_NET_TER_HIER.PAR_ROW_ID, CX_NET_TER_HIER.DB_LAST_UPD, CX_NET_TER_HIER.AREA_CODE, CX_NET_TER_HIER.AREA_CODE_NAME, CX_NET_TER_HIER.BILL_INTG_ID, CX_NET_TER_HIER.CITY, CX_NET_TER_HIER.DB_LAST_UPD_SRC, CX_NET_TER_HIER.NAME, CX_NET_TER_HIER.PRODUCT_ALIAS, CX_NET_TER_HIER.REFRENCE_ID, CX_NET_TER_HIER.TERRITORY_ID, CX_NET_TER_HIER.TERRITORY_TYPE, '12775'
FROM CX_NET_TER_HIER
WHERE
(((CASE WHEN CX_NET_TER_HIER.LAST_UPD = TO_TIMESTAMP('$$CDC_STRT_TS', 'MM/DD/YYYY HH24:MI:SS') THEN 0 WHEN CX_NET_TER_HIER.LAST_UPD > TO_TIMESTAMP('$$CDC_STRT_TS', 'MM/DD/YYYY HH24:MI:SS') THEN 1 WHEN CX_NET_TER_HIER.LAST_UPD < TO_TIMESTAMP('$$CDC_STRT_TS', 'MM/DD/YYYY HH24:MI:SS') THEN -1 ELSE NULL END) >= 0) AND ((CASE WHEN CX_NET_TER_HIER.LAST_UPD = TO_TIMESTAMP('$$CDC_END_TS', 'MM/DD/YYYY HH24:MI:SS') THEN 0 WHEN CX_NET_TER_HIER.LAST_UPD > TO_TIMESTAMP('$$CDC_END_TS', 'MM/DD/YYYY HH24:MI:SS') THEN 1 WHEN CX_NET_TER_HIER.LAST_UPD < TO_TIMESTAMP('$$CDC_END_TS', 'MM/DD/YYYY HH24:MI:SS') THEN -1 ELSE NULL END) < 0))
Oracle Fatal Error].
The parameter file looks like:
[s_CRM_10_020_LoadToStageTableCXNETTERHIER]
$$CDC_LAST_TS=01/01/2014 01:01:01
$$CDC_STRT_TS=02/01/2014 01:01:01
$$CDC_END_TS=03/01/2014 01:01:01
I have tried using 03/01/2014_01:01:01 in parameter file to avoid the space and set the session DateTime Format String to handle the underscore, but this does not help.
I think if there is some way to avoid the enclosing into single quotes in case of string or avoiding the space error in case of date/timestamp then the session will run fine.
Any help is really appreciated. Thank you!
You probably declared the variable as a Expression Variable (IsExprVar = True). So, it first expands the variable, and then evaluates the expression in filter causing the error due to the space.
The same reason, why the variable is not expanded in SQ.
Try making IsExprVar=False. Or, you can define the variable as string, and convert it to date in the expression.