Our World Statistics Day conversations have been a great reminder of how much statistics can inform our lives. Do you have an example of how statistics has made a difference in your life? Share your story with the Community!
Choose Language Hide Translation Bar
Highlighted
Level III

## how to extract data with criteria of JMP table values

Hi

i am trying to extract data for specifc lot list

for now i extract all data and filter the data i need

but

how do i update my data extraction script to take values directly  from JMP table?

example of data table:

New Table( "lot list",

New Column( "lot",

Character,

Nominal,

Set Values( {"AB457", "AB589", "XY671", "XY001", "XY187"} )

)

);

example of extract criteria for the lots in the table

"SELECT * FROM \!"LOTTABLE\!"

WHERE \!"LOT\!" in ('AB457','AB589','XY671','XY001','XY187')

;

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User

## Re: how to extract data with criteria of JMP table values

You can use CONCAT ITEMS to create an IN list from a JMP list.  For example:

dt = New Table( "lot list",

New Column( "lot",

Character,

Nominal,

Set Values( {"AB457", "AB589", "XY671", "XY001", "XY187"} )

)

);

lot_list = Column( dt, "lot" ) << Get values;

in_list = "('" || Concat Items( lot_list, "', '" ) || "')";

sql_string = "SELECT * FROM mytable m WHERE m.lot_number IN " || in_list;

3 REPLIES 3
Highlighted
Staff

## Re: how to extract data with criteria of JMP table values

If I understand correctly, you want to submit a SQL query from JMP using values held in a table. The code below uses a brute force approach to build the query string, and may give you some ideas. In this example, the values came from a user interface that required the user to select one and only one name, and one or more products.

reps = repsObj << GetSelected;

prod = prodObj << GetSelected;

if (NItems(reps)!=1, Beep(); Dialog("WARNING: You need to select a name"); Throw());

if (NItems(prod)<1, Beep(); Dialog("WARNING: You need to select at least one product"); Throw());

// Build the resulting query

SQLQuery = "SELECT * FROM Final WHERE salesrep_name = '"||reps[1];

if (NItems(prod)==1,

// Simple to build the query

SQLQuery = SQLQuery||"' AND product_group = '"||prod[1]||"';",

// else takes a bit more work

prodPart = "' AND (product_group = '"||prod[1];

for(i=2, i<=NItems(prod), i++, prodPart = prodPart||"' OR product_group = '"||prod[i]);

prodPart = prodPart||"');";

SQLQuery = SQLQuery||prodPart;

);

// Show(SQLQuery); // Debug

Highlighted
Level III

## Re: how to extract data with criteria of JMP table values

i manged to create array, now i need to find a way to concat it

dt = New Table( "lot list",
New Column( "lot",
Character,
Nominal,
Set Values( {"AB457", "AB589", "XY671", "XY001", "XY187"} )
)
);

lots = lot << Get values;
//for example
Show (lots[2]);
/*:

lots[2] = "AB589";

Highlighted
Super User

## Re: how to extract data with criteria of JMP table values

You can use CONCAT ITEMS to create an IN list from a JMP list.  For example:

dt = New Table( "lot list",

New Column( "lot",

Character,

Nominal,

Set Values( {"AB457", "AB589", "XY671", "XY001", "XY187"} )

)

);

lot_list = Column( dt, "lot" ) << Get values;

in_list = "('" || Concat Items( lot_list, "', '" ) || "')";

sql_string = "SELECT * FROM mytable m WHERE m.lot_number IN " || in_list;

Article Labels

There are no labels assigned to this post.