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

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

chris_dennis — Wed, 15 Jul 2015 14:45:23 GMT

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

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

pmroz — Mon, 27 Jul 2015 12:18:14 GMT

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')"

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

chris_dennis — Wed, 29 Jul 2015 22:55:32 GMT

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