Using the 'is in' Operator in a Query

The Query Condition determines which records will be returned by a query. This is akin to a row on a database spreadsheet.

Sometimes you might want to pull a list of individuals who meet any of several, similar criteria. For example, when advertising for an upcoming event, you might want to pull a list of all of the individuals who registered for similar events in the past. There are multiple ways to approach this need.

You could create multiple saved queries and then use the Audience functionality to combine them.

You could create a query with multiple lines and lots of or lines:

You could also create a single query line using the is in operator.

The is in operator is ideal for searching columns that contain short values, such as a code. It is not ideal for searching on names, since the values entered must match exactly; the more data entered, the greater the chance for an error. When you select the is in operator, the data input area will change to a text box. Here, you can enter the list of values you want to search for.

Each value should be enclosed within single quotes, separated by commas. When created from the Event Registrant Run Query, the above query line would return all individuals who registered for any of the three events listed.

The benefit of using is in is that you can very quickly create a single query line that pulls records meeting any of multiple similar criteria. The disadvantage to it is that you cannot use the Ask at Runtime feature; any changes to the variables must be made directly in the query.

Using Is In with SQL Code

The Is In operator can be used with SQL Code to perform more advanced filtering.

For example, suppose you're trying to run a query of every record that was added yesterday or today. You could do this with a Is In operator that will pull in any records that were added yesterday or later:

The specific SQL typed into the Value box is:

SELECT ind_cst_keyFROM co_individual (nolock)WHERE ind_add_date > dbo.av_begin_of_day( dateadd(dd, -1 , GETDATE() )

Since the Column to Query is Primary Key which is actually the ind_cst_key column, the SQL command you write must return a record set of the same general type. Therefore, the SQL command returns a single column: ind_cst_key. The function av_begin_of_day is a simple UDF that will take in a datetime and remove the time portion. The DATEADD function is a SQL function that will adjust a provided date. In this case, we are returning the current date minus 1 day.

You can use this feature to write more nuanced and advanced SQL to create any filter you need. For example, if you were looking for any members who rejoined yesterday, you'd need to have a Column to Query of a Customer Key and the SQL would be:

SELECT mbr_cst_key FROM mb_membership (nolock) WHERE mbr_rejoin_date BETWEENdbo.av_begin_of_day(DATEADD(dd, -1, getdate( ))) AND dbo.av_end_of_day(DATEADD(dd,-1,getdate()))

The WHERE clause uses the SQL BETWEEN keyword. The UDF av_end_of_day will ensure that the date extends through the last millisecond of the date.

Tip: The Is In operator also is used to develop Sub-Queries.