Support Questions

Find answers, ask questions, and share your expertise
Celebrating as our community reaches 100,000 members! Thank you!

NiFi expression language inside ExecuteSQL statements

Expert 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?


New Contributor

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

Master Guru

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