SQL Query Error when selecting separate columns

Question

I do not understand the error which appears in my SQL Query. If i choose in SQL Select * -> it is working fine and i do get the table, however if i select any of the column/s it is giving me an Error:

Error in $<-.data.frame(*tmp*, "PROBE", value = structure(integer(0), .Label = character(0), class = "factor")) :
replacement has 0 rows, data has 1427

Here is my SQL code:

if(input$filter == 1){
        sqlOutput <- reactive({
          sqlInput <- paste("select * from DWH.PROBE where DWH.PROBE.Nr =",paste0("'",d(),"'"), "And DWH.PROBE.AB BETWEEN",input$abmfrom, "AND",input$abmto,"ORDER BY Datum asc")
          print(sqlInput)
          dbGetQuery(con$cc, sqlInput)
        })
      }else{
        sqlOutput <- reactive({
          sqlInput <-  paste("select * from DWH.PROBE where DWH.PROBE.S BETWEEN",d2(), "AND",input$sgehalt2, "And DWH.PROBE.AB BETWEEN",input$abmfrom2, "AND",input$abmto2,"ORDER BY Datum asc")
          dbGetQuery(con$cc, sqlInput)
        })}

And if i just add to those SQL Queries

select DWH.PROBE.S, DWH.PROBE.AB.. from DWH.PROBE

Then it comes above mentioned Error.

Additionally i need to say if i will use this SQL Query in a simple code:

rs <- dbSendQuery(con, paste("select DWH.PROBE.AB, DWH.PROBE.S from DWH.PROBE where DWH.PROBE.Nr = '50' And DWH.PROBE.AB BETWEEN 40 AND 50 ORDER BY Datum asc"))
data <- fetch(rs)

It is giving me the results...

Any ideas?

[EDIT *as my question is not a duplicate]

The question posted here: http://stackoverflow.com/questions/32048072/how-to-pass-input-variable-to-sql-statement-in-r-shiny actually has nothing to do with my topic. As we can see the error in this post:

Error in .getReactiveEnvironment()$currentContext() : Operation not allowed without an active reactive context. (You tried to do something that can only be done from inside a reactive expression or observer.)

I do not have a problems with passing input variable to sql statement and additionally if you can see in my SQL: The Query is in reactive context!:

sqlOutput <- reactive({...

The solution for above question was:

to put SQL Query in reactive context which is not a thing in my case

[EDIT 2] -> bits related to sqlOutput()

Here is a bit of code related to sqlOutput() which i am using in my Shiny App (at the moment this is the only bit because i am stuck with SQL Query)

output$tabelle <- DT::renderDataTable({
    data <- sqlOutput()
    data$PROBE <- as.factor(as.character(data$PROBE))
    data
    }, rownames=TRUE, filter="top", class = 'cell-border stripe', 
                      options = list(pageLength = 100, lengthMenu=c(100,200,500), columnDefs = list(list(width = '200px', targets = "_all"),list(bSortable = FALSE, targets = "_all"))))

Thanks

Possible duplicate of How to pass input variable to SQL statement in R shiny? — Pork Chop
– Pork Chop, Commented Jun 9, 2016 at 8:21
Its is not duplicate @Pork Chop if you have noticed my SQL Query is in reactive context* so that is not the problem — Mal_a
– Mal_a, Commented Jun 9, 2016 at 8:36
@zx8754 I am not creating any new columns, neither subsetting it. I just want to get the specific columns from databank (in the databank there are 50 columns, i need just 10 of them) — Mal_a
– Mal_a, Commented Jun 9, 2016 at 8:46

zx8754 · Accepted Answer · 2016-06-09 08:12:44Z

0

Error doesn't relate to SQL statements, however, try changing your code to below:

sqlOutput <- reactive({
  if(input$filter == 1){
    sqlInput <- paste("select * from DWH.PROBE where DWH.PROBE.Nr =",paste0("'",d(),"'"), "And DWH.PROBE.AB BETWEEN",input$abmfrom, "AND",input$abmto,"ORDER BY Datum asc")
  } else {
    sqlInput <- paste("select * from DWH.PROBE where DWH.PROBE.S BETWEEN",d2(), "AND",input$sgehalt2, "And DWH.PROBE.AB BETWEEN",input$abmfrom2, "AND",input$abmto2,"ORDER BY Datum asc")
  }
  dbGetQuery(con$cc, sqlInput)
})

answered Jun 9, 2016 at 8:12

zx8754

56.7k12 gold badges131 silver badges229 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mal_a Over a year ago

if the problem is not in SQL , then where possibly it can be, as an easy SQL Query which i posted at the end, gives the desired Table...

zx8754 Over a year ago

@Malvina_a we cannot really tell, add your full code (at least bits where it is related to this SQL output)

Mal_a Over a year ago

I have just edited my question and i pasted there bit of my code where sqlOutput() is used

Collectives™ on Stack Overflow

SQL Query Error when selecting separate columns

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related