SQL iQuery Script Built-in Function

ARRAY_STRING / ARRAY_STG / ARR_STG / ARR_STRING

Convert Array Values to a String with optional separator.

The ARR_STRING built-in functions converts a Session Variable with multiple values into one long value. An optional separator may be specified.

Syntax

• eval &string = array_string( < Session Variable > [,  separator ][, wrapper ] )
• eval &fixedString = array_string( < Session Variable >,  length )

The first format of ARRAY_STRING() built-in function returns the values assigned to the Session Variable as a delimited string. The delimiter and optional wrapper may be specified to control the characters used for those purposes.
The second form of ARRAY_STRING() built-in function returns the values assigned to the Session Variable by assigning each value a fixed length in the result. The user may specify the length each value occupies in the resulting string.

Parameters

1. A session variable with 1 or more values.
2. The separator between each returned value when more than one value is returned or the fixed-length of each value returned.
• The 2nd parameter has special handling when a double and single quote is specified. A single quote or double quote is applied to the front and back of the resulting values, whereas any other characters must have 1 or 2 character specified. If only one is specified, it is only applied to the front or left-side of the resulting values. When 2 characters are specified, the first one is applied to the front/left side of the value, while the 2nd character is applied to the back/right side of the value.
  
3. A wrapper (up to 2 characters in length) that is used to surround each resulting value (but not the separator). This parameter is only valid if parameter 2 is specified but not as a length argument.
   

SELECT NAME  into &libl                            
    FROM qsys2.library_list_info       
    WHERE TYPE in ('CURRENT','USER')            
    order by colno;                                
                                                   
eval &liblCSV = array_stg(&libl,',','\'');         
select * from qsys2.output_queue_info              
  where OUTPUT_QUEUE_LIBRARY_NAME in (&LIBLCSV);   
    

What"s going on here: The IBM i Db2 Service named LIBRARY_LIST_INFO is run with each library being inserted as a value into the &LIBL variable. I include on the User-portion of the library list along with *CURLIB. Effectively we've omitted the System library list library names along with any Product Library name. Next we use ARRAY_STG to convert the values in &LIBL into a Comma-Separated List, such as: 'QGPL','DEVTEST','TESTDATA',QTEMP','COZTOOLS'

You will note that the 3rd parameter contains a single quote and is "escaped". The back slash is how you escape special characters in SQL iQuery Script, except on SQL statements which use the SQL-notation.
That new variable is inserted into the SQL SELECT statement's WHERE clause. The list of Output Queue names is restricting it to just the entries in the &LIBLCSV variable, which is the user portion of the library list. On my system, this brought up the Output Queues in QGPL and DEVTEST, but avoided those in QUSRSYS which is traditionally where most of the *OUTQ objects are created.