nifi expression value for null and datetime

Question

I have created data flow which will transfer SQL server data from DB to DB in nifi and there a datetime column and it also contains some null value in source table, now i want to transfer that column with datetime value and also null values same time to the datetime column in target table. I'm unable to find right expression to put it in update attributes processor.

I'm using below expression in update process:

sql.args.1.name : CR_LINE_CMTD_START_DT sql.args.1.type : 93 sql.args.1.value :${CR_LINE_CMTD_START_DT:toDate("yyyy-MM-dd HH:mm:ss.SSS"):toNumber()}

Please help to fix this

Thanks sam

Answer 1

Are you getting errors when a null value comes along, or for every input?

In UpdateAttribute there is an Advanced UI (click the Advanced button on the bottom left corner of the dialog) where you can update the value of sql.args.value.1 based on conditions. So you could use ${CR_LINE_CMTD_START_DT:notNull()} as one condition, where the action is what you have above. The you could add a condition for ${CR_LINE_CMTD_START_DT:isNull()} to set the value to ${CR_LINE_CMTD_START_DT} (or perhaps leave the value blank if you can).

Please see the UpdateAttribute "Additional Details" page for more information on the Advanced usage. Also as of NiFi 1.2.0 (not yet released at the time of this answer), NIFI-3206 will add an ifElse() function to Expression Language, which should make it easier to do inline.