Support Questions
Find answers, ask questions, and share your expertise

NiFi expression language inside ExecuteSQL statements

NiFi expression language inside ExecuteSQL statements

Contributor

We have a custom query that we wish to put inside the ExecuteSQL processor, but this one proves a bit tricky:

SELECT * FROM mytable AS OF TIMESTAMP TO_TIMESTAMP('${now():format('yyyy-MM-dd HH:mm:ss')}', 'YYYY-MM-DD HH24:MI:SS');

The processor is CRON driven where we run it every 15 minutes. The expression language inside the first single quotes is NiFi's expression language to get the current time in the format specified. Note that the expression language:

${now():format('yyyy-MM-dd HH:mm:ss')}

itself uses single quotes. I tried substituting double quotes for the Oracle query, but Oracle won't accept the syntax as valid. So how do we substitute the intended date-time string inside the first single-quotes?

2 REPLIES 2

Re: NiFi expression language inside ExecuteSQL statements

New Contributor

Why don t you stage the $now():format(...) in an UpdateAttribute processor first and pass the new variable to your SQL query ?

Re: NiFi expression language inside ExecuteSQL statements

Super Guru

The documentation says "The Expression Language allows single quotes and double quotes to be used interchangeably". Try double-quotes in your EL expression.