Subscribe Bookmark RSS Feed

In()

msharp

Super User

Joined:

Jul 28, 2015

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.

4 REPLIES
txnelson

Super User

Joined:

Jun 22, 2012

dt<<select where(N Rows(Loc(list,:col))>0);

This should do what you want

Jim
ms

Super User

Joined:

Jun 23, 2011

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));

msharp

Super User

Joined:

Jul 28, 2015

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

Super User

Joined:

Jun 23, 2011

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[]));