Rsqlite Query with User Specified Variable in the Where Field

RSQLite query with user specified variable in the WHERE field

SQLite will only see the string passed down for the query, so what you do is something like

  sqlcmd <- paste("SELECT * FROM annual WHERE fiscal=", fiscal.year, sep="")
  data.annual <- dbGetQuery(db, sqlcmd)

The nice thing is that you can use this the usual way to unwind loops. Forgetting for a second that you have ram restrictions, conceptually you can do

  years <- seq(2000,2010)
  data <- lapply(years, function(y) {
     dbGetQuery(db, paste("SELECT * FROM annual WHERE fiscal=", y, sep="")
  }

and now data is a list containing all your yearly data sets. Or you could keep the data, run your regression and only store the summary object.

SQLite Select from where column contains string?

SELECT * FROM users WHERE column LIKE '%mystring%' will do it.

LIKE means we're not doing an exact match (column = value), but doing some more fuzzy matching. "%" is a wildcard character - it matches 0 or more characters, so this is saying "all rows where the column has 0 or more chars followed by "mystring" followed by 0 or more chars".

How to write a SQL query to pull a value from a nested json object identified by a variable field name

When you want to recursively walk through the fields of an entire object and its contents, you need json_tree():

SELECT j.value
FROM devices AS d
JOIN json_tree(d.device) AS j
WHERE j.key = 'dot11.advertisedssid.ssid';

gives

value         
--------------
SampleFES-WiFi

when run on a table holding a fixed version of that sample object.

Is there an sqlite function that can check if a field matches a certain value and return 0 or 1?

In SQLite, boolean values are just integer values 0 and 1, so you can use the comparison directly:

SELECT col1, col2 = 200 AS SomeFunction FROM MyTable

SQL - How to reference outerquery new column in subquery

Assuming that you actually want to show the result but not the 'equation 3/4' the following should do what you want:

select 
    id
  , sum(var1/var2) as summed_var 
from Tble 
group by id 

All expressions other than involving aggregation results (i.e. those standing alone, or inside aggregation functions like SUM, COUNT, etc) are evaluated for each row( which means that var1/var2 is calculated for each row), then the aggregation (SUM) is performed for each group formed by Id (GROUP BY Id)

Is this what you seek?

Pass R variable to RODBC's sqlQuery?

Build the string you intend to pass. So instead of

example <- sqlQuery(myDB,"SELECT * FROM dbo.my_table_fn (x)")

do

example <- sqlQuery(myDB, paste("SELECT * FROM dbo.my_table_fn (", 
                                x, ")", sep=""))

which will fill in the value of x.

R dbGetQuery with dynamic string

Since I believe R DBI drivers have not yet implemented multiple SQL statements support, dbGetQuery only returns first statement.

Hence, you need to iteratively run your sqlcmd for multiple SQL statements such as with lapply to return a list of dataframes, followed by an rbind call for single master dataframe:

Id <- c(34, 22, 86)
sqlcmd <- paste("select col1, col2 from DB where ItemId =", Id, sep="")

# LIST OF DATAFRAMES
df_list <- lapply(sqlcmd , function(x) dbGetQuery(conn, x)) 

# FINAL DATAFRAME
final_df <- do.call(rbind, df_list)

Alternatively, use a UNION or UNION ALL for one SQL statement.

Id <- c(34, 22, 86)
sqlcmd <- paste("select col1, col2 from DB where ItemId =", Id, sep="")

single_sql <- paste(sqlcmd, collapse = " UNION ")
final_df <- dbGetQuery(conn, single_sql)

Or still use OR:

single_sql <- paste("select col1, col2 from DB where ItemId =", 
                    paste(Id, collapse=" OR ItemId = "))

final_df <- dbGetQuery(conn, single_sql)

SQLite string contains other string query

Using LIKE:

SELECT *
  FROM TABLE
 WHERE column LIKE '%cats%'  --case-insensitive

Use variable with regex in string::str_detect in dbplyr SQL query

With the help of a colleague, I have a solution to force evaluation of a variable with regex in string::str_detect:

tbl(con, "mtcars") %>%
  filter(str_detect(rowname, {{my_string}}))

---

Related Topics