| Title: | 'SQL' Case Statement Generator |
|---|---|
| Description: | Includes built-in methods for generating long 'SQL' CASE statements, and other 'SQL' statements that may otherwise be arduous to construct by hand. The generated statement can easily be concatenated to string literals to form queries to 'SQL'-like databases, such as when using the 'RODBC' package. The current methods include casewhen() for building CASE statements, inlist() for building IN statements, and updatetable() for building UPDATE statements. |
| Authors: | Leoson Hoay [aut, cre]
|
| Maintainer: | Leoson Hoay <[email protected]> |
| License: | MIT + file LICENSE |
| Version: | 0.2.0 |
| Built: | 2024-10-31 21:13:46 UTC |
| Source: | CRAN |
This function constructs a CASE..WHEN,,THEN statement from a mapping file or dataframe. It assumes that the first column of the mapping data contains the original WHEN values, and the second column contains the THEN values (the values to be mapped to.)
casewhen(inputfile = NULL, header = FALSE)casewhen(inputfile = NULL, header = FALSE)
inputfile |
Mapping dataframe OR path to the mapping file |
header |
If reading a csv file, TRUE if the file includes a header row, FALSE if it does not include a header row. |
A string that represents the constructed CASE statement
input <- Data_Frame <- data.frame(Training = c("Strength", "Stamina", "Other"), Duration = c(60, 30, 45)) result <- casewhen(inputfile = input, header = TRUE)input <- Data_Frame <- data.frame(Training = c("Strength", "Stamina", "Other"), Duration = c(60, 30, 45)) result <- casewhen(inputfile = input, header = TRUE)
This function constructs an IN statement from a mapping file or dataframe. It assumes that the first column of the data contains the list of values to check for.
inlist(inputfile = NULL, header = FALSE)inlist(inputfile = NULL, header = FALSE)
inputfile |
Dataframe OR path to the mapping file |
header |
If reading a csv file, TRUE if the file includes a header row, FALSE if it does not include a header row. |
A string that represents the constructed CASE statement
input <- Data_Frame <- data.frame(Training = c("Strength", "Stamina", "Other")) result <- inlist(inputfile = input, header = TRUE)input <- Data_Frame <- data.frame(Training = c("Strength", "Stamina", "Other")) result <- inlist(inputfile = input, header = TRUE)
This function constructs an UPDATE statement from a mapping file or dataframe. It assumes that the first column of the data contains the key column and list of keys for the rows where the corresponding other columns have to be updated. It also assumes that the header for the data includes the column names. The function will generate one UPDATE statement for each row in the data.
updatetable(inputfile = NULL, tablename = NULL)updatetable(inputfile = NULL, tablename = NULL)
inputfile |
Dataframe OR path to the mapping file |
tablename |
Name of the SQL table |
A string that represents the constructed UPDATE statement(s)
input <- Data_Frame <- data.frame(Training = c("Strength", "Stamina", "Other"), Pulse = c(100, 150, 120), Duration = c(60, 30, 45)) result <- updatetable(inputfile = input, tablename = "myTable")input <- Data_Frame <- data.frame(Training = c("Strength", "Stamina", "Other"), Pulse = c(100, 150, 120), Duration = c(60, 30, 45)) result <- updatetable(inputfile = input, tablename = "myTable")