Getting problem with Run time parameter by using RCP

[madsongtel]

Hi,

My requirement is "get the data from one db2 source and load into other db2 target table by using RCP"
My job is like DB2 connector-->Transfomrer-->DB2 connector
It is RCP enabled.

Source db2 query is "Select a.SE_NO, case when(select f_dt from stg_filedt) between a.EFF_DT AND a.END_DT then ''Y'' when a.END_DT is null AND a.EFF_DT is not null then ''Y'' else ''N'' end as FEE_efective_ind From MER_FEE a ,MERCHANT_ACC b where a.SE_NO=b.SE_NO with ur"

Target db2 is simple insert.


For "select f_dt from stg_filedt" query we have created one job it will load into one sequential file.(only one record)
Then we have one routine that takes the sequential file data and assign into one variable.

So i changed the source query as "Select a.SE_NO, Case when (\'Rtn_Ext_Fdate.$ReturnValue\') between a.EFF_DT and a.END_DT Then \'Y\' When a.END_DT is null And a.EFF_DT is not null Then \'Y\' Else \'N\' End as FEE_EFFECTIVE_IND From MER_FEE a ,MERCHANT_ACC b where a.SE_NO=b.SE_NO with ur"

I am getting error as DB2_Ext_Table: DB2 reported: SQLSTATE = 42601: Native Error Code = -104: Msg = [IBM][CLI Driver][DB2/AIX64] SQL0104N An unexpected token "'Rtn_Ext_Fdate.$ReturnValue'" was found following

i have tried lot of time, but couldn't get it.
Could you guys help me please.

[ray.wurlod]

Do you need sharp signs around Rtn_Ext_Fdate.$ReturnValue ?

[madsongtel]

Do you need sharp signs around Rtn_Ext_Fdate.$ReturnValue ? ...

Yes ..we have to give...otherwise it is giving syntax problem.

Now my problem is solved.
Below is the way to give in query:
"Select a.SE_NO, Case when to_date(\'" : Rtn_Ext_Fdate.$ReturnValue : "\', \'YYYY-MM-DD\') between a.EFF_DT and a.END_DT Then \'Y\' When a.END_DT is null And a.EFF_DT is not null Then \'Y\' Else \'N\' End as FEE_EFFECTIVE_IND From MER_FEE a ,MERCHANT_ACC b where a.SE_NO=b.SE_NO with ur"


The main thing is need to give "colon" before and after of routine return value.

[ray.wurlod]

Sharp sign is # for the non-musical among you.

There's none in the query as posted.