big_in: big_in

In Maritimes/Mar.utils: A suite of functions used by a variety of Maritimes packages

big_in

Description

In Oracle, IN statements are limited to 1000 elements. When dynamically extracting data based on values a dataframe, it's pretty easy to exceed this limit. This function converts huge vectors into a format that Oracle can handle, returning many more than 1000 results.

Usage

big_in(vec = NULL, vec.field = NULL, isStrings = FALSE)

Arguments

vec	The default value is NULL.
vec.field	The default value is NULL. This is the name of the field that the resultant IN statement will search for the values of vec
isStrings	The default value is FALSE. This determines whether the values in the resultant IN statement are surrounded by apostrophes. For numeric vectors, it should be FALSE, which will generate a statement like field IN (1,2,3). For characters/strings, it should be set to TRUE, so it will surround the values with apostrophes and generate a statement like field IN ('Archer', 'Lana', 'Krieger').

Value

This returns a (potentially really long) valid IN string that can be included in a SQL query as a WHERE condition. It must be prefaced by "WHERE", or "AND" depending on whether other conditions are present. If vec has no valid values, the function will return "1=1" which will allow SQL statements to run.

Note

The logic for this was stolen from our friends at StackOverflow.com. Specifically, Sergey11g's response to https://stackoverflow.com/questions/400255/how-to-put-more-than-1000-values-into-an-oracle-in-clause

Author(s)

Mike McMahon, [e-mail]

See Also

Other util: Mode(), SQL_in(), st_err()

Examples

## Not run: 
bigVector <- c(1:2000)
raw <-  big_in(vec=c(1:2000), vec.field = "field2", isStrings = F)
raw
[1]  "('_dOh_', field2) IN (('_dOh_',1),('_dOh_',2),...('_dOh_',2000))

qry <- paste0("SELECT field1, field2 
from TABLE 
where field1 = 'value' 
AND ",big_in(vec=c(1:2000), vec.field = "field2", isStrings = F))
qry
[1] "SELECT field1, field2 
from TABLE 
where field1 = 'value' 
AND ('_dOh_', field2) IN (('_dOh_',1),('_dOh_',2),<...>('_dOh_',2000))

## End(Not run)

Maritimes/Mar.utils documentation built on Feb. 12, 2024, 11:38 p.m.