Malvina_a Malvina_a - 1 year ago 184
SQL Question

Shiny Reactive SQL: WHERE clause

I am new to SQL and i am trying to figure out how i can "select all" in WHERE clause.

Let me make it bit more clearer why i would like to achieve that:

I do have reactive SQL in my Shiny App. The user can filter at least three different variables in my SQL:
enter image description here

In this case user can choose Kunde (Customer), Abmessung (Diameter) and Date. I do have hard time to figure out when the user wants to filter only one/two variables and not three (the combinations of possibilities inside is huge, and writing SQL Query in if statement for each is quite a thing). So for example the user would like to have Kunde (Customer) filtered but all the Abmessung (Diameters) kept.

Here is a sample SQL Query which i have used:

select * from x.xy where kdname IN (",paste0("'",paste(input$kunde,
collapse="', '"),"'"),") and abmessung IN (",paste0("'",paste(input$abm,
collapse="', '"),"'"),") and dati_create between
to_date('",format(input$date[1], '%d.%m.%Y'),"','') and
to_date('",format(input$date[2], '%d.%m.%Y'),"','') + (86399/86400)

Is there a possibility in SQL to use some kind of "*" in WHERE clause?

Answer Source

Why not simply do it using if to each condition and than paste collapse it

( i think there isn t way to do what your want without "if\esle" or "case" or "nvl" or "decode" so you need to hardcode it)


#input=list("kunde"="","abm"="",date=c("","")) # for test
sql_main="select * from x.xy "
sql_cond[1]=  if(input$kunde==""){NULL}else{paste0("kdname IN ('",paste(input$kunde,collapse="','"),"')")}
sql_cond[2]=  if(input$abm==""){NULL}else{paste0("abmessung IN ('",paste(input$abm,collapse="', '"),"')")}
sql_cond[3]=  if(input$date[1]==""|input$date[2]==""){NULL}else{paste0("dati_create between to_date('",as.character(as.Date(input$date[1]),'%d.%m.%Y'),"','') and
to_date('",as.character(as.Date(input$date[2]),'%d.%m.%Y'),"','') + (86399/86400)")}
sql_cond=sql_cond[!sapply(sql_cond,is.null)]# needed to del NULL in list
sql_cond_all=paste(sql_cond,collapse  =" and ")

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download