IN

valuet IN (value1 AND value2 . . . .) or valuet IN subquery

The first form permits using a list of values instead of a subquery.

The second form uses a subquery that is evaluated to produce an intermediate result, against which further processing can be performed.

The IN predicate lets you use a list of values instead of a subquery, or will introduce a subquery.

IN differs from ISMEMBER because IN is performed on the database (if possible), while ISMEMBER is computed on the server.

The IN predicate has a negative version, NOT IN. The format for this is identical to IN. NOT IN is true only if the provided value is not found in the values returned by the subquery.

This macro is available in IBM® Interact.

When using IN in IBM® Interact, you can only use the value IN (value1 AND value2 . . . .) syntax.

TEMP = IN(25, COLUMN(1...10))

Returns the specified column(s) from a data range

TEMP = IN("cat", COLUMN("cat", "dog", "bird"))

Creates a new column named TEMP containing the value one.

TEMP = IN(V1, V1)

Creates a new column named TEMP containing all ones.

TEMP = IN(V1, V2)

Creates a new column named TEMP, where each value is a one if the corresponding row of column V1 contains a value in column V2, else a zero.

SELECT title, current_rental_price FROM movie_titles WHERE our_cost IN (14.95, 24.95, 29.95 ) ;

means that all movies that cost either $14.95, $24.95, or $29.95 will evaluate as TRUE to the IN predicate and will, in turn, produce a list of titles and accompanying current rental prices for those movies.

UPDATE movie_titles SET current_rental_price = (regular_rental_price * .9) WHERE title IN ( SELECT movie_title FROM movie_stars WHERE actor_last_name = 'Stewart' AND actor_first_name = 'James' ) ;

After the subquery produces its list of movie titles, the list is processed against the MOVIE_TITLES table and the appropriate rental prices are discounted for Philadelphia Story, It's a Wonderful Life, and so on.

Copyright IBM Corporation 2015. All Rights Reserved.