cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
chris_dennis
Level III

JSL get values from a table and make into a text string that can be used as an input in another datebase query

In my script I have multiple data base queries.   From one query it get a column with a list of lot numbers.  I want to take the values from this column and make them into a local variable that I can use in the next data query to pull data from a different database.

using a script line below I get the value string.

cassette = SLC:DESTINATION_CASS << get values;

result

cassette = {"SPQ093146 1"}

How can I get rid of the "{" from this item. This example only one value listed, but result could have multiple values

1 ACCEPTED SOLUTION

Accepted Solutions
pmroz
Super User

Re: JSL get values from a table and make into a text string that can be used as an input in another datebase query

The get values message returns a list.  You can refer to individual list elements using cassette[1], cassette[2] etc.

But there's an easier way to convert a set of values in a column to something usable.  I use this approach to create an IN LIST for an Oracle SELECT statement.  Use the concat items function like so:

slc = New Table( "Cassette Data", Add Rows( 3 ),

     New Column( "DESTINATION_CASS", Character, Nominal,

           Set Values( {"aa", "bb", "cc"} ) )

);

cassette_list = slc:destination_cass << get values;

// Convert the list to an Oracle IN list

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

Here are the results:

"('aa', 'bb', 'cc')"


View solution in original post

2 REPLIES 2
pmroz
Super User

Re: JSL get values from a table and make into a text string that can be used as an input in another datebase query

The get values message returns a list.  You can refer to individual list elements using cassette[1], cassette[2] etc.

But there's an easier way to convert a set of values in a column to something usable.  I use this approach to create an IN LIST for an Oracle SELECT statement.  Use the concat items function like so:

slc = New Table( "Cassette Data", Add Rows( 3 ),

     New Column( "DESTINATION_CASS", Character, Nominal,

           Set Values( {"aa", "bb", "cc"} ) )

);

cassette_list = slc:destination_cass << get values;

// Convert the list to an Oracle IN list

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

Here are the results:

"('aa', 'bb', 'cc')"


chris_dennis
Level III

Re: JSL get values from a table and make into a text string that can be used as an input in another datebase query

This works great.  thanks so much for the help on this item.