QuerySauce: The Sauce for Dishing Data

In bfatemi/sqlsauce: The Secret Sauce for Dishing Data

Description

The function QuerySauce and it's helper WhereSauce provide the the ability to effortlessly build a valid sql query string

Usage

QuerySauce(tbl = NULL, top = NULL, cols = NULL, wh = NULL,
  where = NULL, verbose = FALSE)

WhereSauce(...)

OpsDT()

QueryTree(ll)

ColSauce(...)

AND(wh, string)

OR(wh, string)

not(...)

Qsauce(tbl = NULL, top = NULL, cols = NULL, wh = NULL, where = NULL,
  verbose = FALSE)

Wsauce(...)

op(x)

ch_op(x)

lop(x)

clop(x)

rop(x)

crop(x)

revop(x)

Arguments

tbl	A character string that represents the sql tablename to query
top	A numeric value indicating whether to return the top N rows from the query execution
cols	An optional character vector specifying columns to return. If not provided, all columns will be returned
wh	The output from a call to WhereSauce. A character string representing a valid where clause
where	Deprecated. Use wh instead.
verbose	A boolean indicating whether to print the constructed query
...	Arguments to create the WHERE statement or as in the case of the not function: One or more logical operations to produce the negated syntax for sql
ll	A list object that wraps the R expressions in order to translate to sql where conditions
string	A string to concat to the supplied where statement
x	A logical operation in the form of an expression

Details

FILL IN DETAIL HERE. See example workflows

Functions

• QuerySauce: A function to build the query

• WhereSauce: A function to the WHERE clause that becomes an input into QuerySauce

• OpsDT: Function to translate R operators to sql ones

• QueryTree: Function to build query

• ColSauce: A function to retrieve the column names associated with the expressions given in the WhereSauce args

• AND: A helper to append arbitrary AND conditions to the return object of Wsauce. See examples

• OR: A helper to append arbitrary OR conditions to the return object of Wsauce. See examples

• not: A function to handle not expressions

• Qsauce: An alias to QuerySauce

• Wsauce: An alias to WhereSauce

• op: Get operator

• ch_op: Get character op

• lop: Get left side op

• clop: Get character left side op

• rop: Get right side op

• crop: Get character right side op

• revop: Reverse operations

Examples

## Not run: 
# STEP 1: Name the configured database
# STEP 2: Name the table to query
# STEP 3: Get the WHERE sauce (optional ingredient for the query sauce)
# STEP 4: Get the Query sauce (main ingredient for the data dish)
#----------------------------------------------------------------------------

db <- "RemoteFE"
tbl <- "SFDC.dbo.PROCEDURES"

#----------------------------------------------------------------------------
# Get procedures of specified subjects that occurred since 2015
#----------------------------------------------------------------------------
psub <- c("HPB", "dVP", "dVC", "dVL") # for WHERE sauce

wh    <- WhereSauce(ProcedureSubject == psub & Year(CreateDate) > 2015)
query <- QuerySauce(tbl, top = 1000, where = wh)

DT    <- xQuery(db, query)

#----------------------------------------------------------------------------
# Alternate workflow:
#   Open/Closing is handled by xQuery unless db is open. In which case,
#   the user can control when to open and close connections
#----------------------------------------------------------------------------

OpenDB(db)  # Open connection

# .... Do work

CloseDB(db) # Close connection

## End(Not run)

bfatemi/sqlsauce documentation built on May 12, 2019, 7:27 p.m.