Subscribe Bookmark RSS Feed

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

Community Trekker

Joined:

Nov 21, 2014

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
Solution

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


2 REPLIES
Solution

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

Community Trekker

Joined:

Nov 21, 2014

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