How do I create a DB query with variable parameters?

[sversienti (Salvatore)]

Hi,
I'd like to set up a parameterized query. I configured my logstash.conf to query my Oracle database. My table has a REF_DATE field valued with a date CHAR(10 BYTE).
I need to make the following query:

`SELECT COUNT (*) FROM MY_TABLE WHERE REF_DATE = <TODAY_DATE>`

This query will be executed one or more time every day and <TODAY_DATE> will be change every day.
I tried to use the :sql_last_value parameter but I did not reach my goal.

Is it possibile to configure <TODAY_DATE> into logstash.conf or sending this parameter from kibana tool?

Could you help me please?
Thank you

[yaauie (Ry Biesemeyer)]

Could this be done with the Oracle CURRENT_DATE?

SELECT COUNT (*) FROM MY_TABLE WHERE REF_DATE = TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD')

[sversienti (Salvatore)]

Thanks! It is useful but in my question I forgot to write that I need to query even for past dates, like for example:

SELECT COUNT (*) FROM MY_TABLE WHERE REF_DATE = TO_CHAR(PAST_DATE, 'YYYY-MM-DD')

How can I pass a variable past date as input to my query?
Thank you.

[yaauie (Ry Biesemeyer)]

That could be done with a GROUP BY clause, which would emit one event per REF_DATE value:

SELECT REF_DATE, COUNT(*) FROM MY_TABLE GROUP BY REF_DATE

[sversienti (Salvatore)]

Thanks yaauie! I understood how to do it.