Error in WHERE Syntax using GLUE package

[danimaiams]

Hi people!

I need to do a "WHERE" filter in my code using GLUE package, like that:

df <- dbGetQuery(
      connection_reportUser,
      query <- glue(
        "select
        round ((aust.DTH_DESENFORNAMENTO - aust.dth_enfornamento),7)*1440 as HF
       
        from
        qt_qts.res_tubo_austenitizacao aust

       WHERE
       HF between 'input$time1' and 'input$time2' <----------------- THE ERROR
       ")

But it show that HF is not recognized in my code. So, i tried to put the full name (aust.DTH_DESENFORNAMENTO - aust.dth_enfornamento) and it worked... BUT, i need to put the full expression, what converts the time to seconds (((aust.DTH_DESENFORNAMENTO - aust.dth_enfornamento),7)*1440). That way, an error appear, cause of the expression.
I'm using ORACLE database

How can I refeer to the HF time with no error in my code?

[jlacko]

The reference to the HF column looks legit; it rather seems you have a problem with gluing the query string together.

I would suggest splitting your code into two steps:

• constructing the query string first
• and only then applying it

That way you can isolate the query string (either via printing or using browser()) and debug it in your favorite DB management kit.

I suspect you might want to convert the contents of the input fields to numbers, and your usage of single and double quotes feels wrong, but I am not a glue expert.

query <- glue(
        "select
        round ((aust.DTH_DESENFORNAMENTO - aust.dth_enfornamento),7)*1440 as HF
       
        from
        qt_qts.res_tubo_austenitizacao aust

       WHERE
       HF between 'input$time1' and 'input$time2' ")

# stop right here! - browser() or what not

df <- dbGetQuery(
      connection_reportUser,
      query)

[jimhester]

You need to use braces to have glue convert the variable names to values, e.g. it should be '{input$time1}'

Also if you plan to do more construction of SQL strings with glue I would encourage you to explore using glue_sql() which is designed for this purpose.

[andresrcs]

This is similar to your other question, so use braces {} and construct the sql query inside a reactive function that way you would have access to input.

Passing parameters with 'input$ID' and 'value'

Hi guys. I need to pass a parameter using some input value, but it's not working. Like: df <- dbGetQuery( connection_reportUser, query <- glue( "select cod_aqa as AQA, from QT_QTS.PLA_ORDEM_PRODUCAO where cod_aqa = 'input$aqateste' ") ) That way, returns no data available in my DATABASE when my Input = 'VS53'. But... when i do the same code using: df <- dbGetQuery( connection_reportUser, query <- glue( "select cod_aqa as AQA, from QT_QTS.PLA_ORDEM_PRODUCAO where cod_aqa = 'VS53' ") ) That way, the program can find and return a value from my DATABASE. My…

[danimaiams]

Guys, thanks for the help.

It worked that way:

...WHERE
ROUND((aust.DTH_DESENFORNAMENTO - aust.dth_enfornamento),7)*1440 between '{input$time1}' and '{input$time2}'`

I just need to put the ROUND inside the WHERE condition.