In()

Question

In SQL there is a command for in(..) where it will find every row in a table that the column specified has a value IN the in(...) statement.  This is nice to quickly find items in a list.  It would be nice if we could do this in JSL.

EX:

dt << Select Where( in(col, list) );

as opposed to:

dt << Select Where( :col == list[1] | :col == list[2] | ... )

The disadvantage for the second method is obvious that you would require knowing the length of the list. 
I can think of several work arounds with for loops, but most of these will run into performance issues in large data sets.

Just add this to my wish list.

msharp · Answer

Thank you!  After a few minutes I finally understand.  Instead of searching the column for the values in the list, we are searching the list for the value found in each cell.

ms · Answer

This simpler version of the same idea works too (Select Where apparently treats [1] as True and [ ] as False):

dt << select where(Loc(list, :col));

ms · Answer

Contains() may do what you wish.

dt = Open("$SAMPLE_DATA/Big Class.jmp");
list = {"JANE", "ALICE", "MICHAEL", "ELIZABETH"};
col = Column(dt, "name");
dt << select where(Contains(list, col[]));