Multiple values in where clause(IN) of select query in Cisco Cloud Portal using single field

[jaisys001]

I can actually pass multiple values in the IN statement of select query using multiple dictionary fields in the data retrieval rule of the AFC in Cisco cloud portal like

#dictionary.field1# = 1 and

#dictionary.field2#=2

select col1,col2 from table1 where col3 in (#dictionary.field1#,dictionary.field2#).

but I want to pass mutiple values in a single field as

#dictionary.field1#=1,2

select col1,col2 from table1 where col3 in (#dictionary.field1#) and the query gives no data because it is taking as '1,2' instead of '1','2'.

Please give the solution for passing multiple values in a single variable to use in IN operator of WHERE clause

[derevan]

Perhaps I am taking your example too literally but the specific query you posted does not require IN. Try

Select col1,col2 from table 1 where (col3=#dictionary.field1#) or (col3=#dictionary.field2#)

If what you had in mind was something more complicated than your example, you can use the following general construct:

Select col1,col2 from table 1 where col3 in

   ((Select #dictionary.field1#)

     Union

     (Select #dictionary.field2#))

The above is the general format. The actual sub-selects will depend on your specific circumstance and possibly your RC DB type.

[jaisys001]

Thanks for your logic but what I need is append the two dictionary values in one dictionary field with comma(,) separator and the Sql query should understand it as two values instead of one though we are passing one dictionary field. Why because the values in the dictionary fields will be populated dynamically in run time.

[derevan]

Ok, I now understand what you are trying to do. Unfortunately, you cannot inject parts of a SQL statement into a DDR through a dictionary field, which always represents a specific value (the comma in your case is attempting injection of a SQL construct to refer to multiple values). One possible solution is to arbitrarily consolidate your list of values using a delimiter that you know will not be in the values themselves such as a colon (:). Let's use 3 values as it serves as a better example.

Set your dictionary field to a single reference to all 3 values of interest, say 'a', 'b', 'c' as:

:a:b:c:  (you can use javascript to create this consolidated dictionary field)

Now your query would look something like the following:

select col1,col2 from table1 where #dictionary.field1t# like '%:'+col3+':%'

This should achieve the desired result.