IN Query for list of string

IN Query for list of string

  

Hi All,

I am writing an advanced query and i want to extract data depend on the list of string

SELECT * 

FROM TEST
WHERE TestCode IN ('Test1', 'Test 2')

How can i achieve that in advanced query?

Hi jace,

While I'm not fond of IN, you can.do exactly as you typed. 

If the list is variable, than you need a query parameter (text) with the inline sql enabled in this parameter and you pass the list on it.

IN (@items)

Where @items is your parameter and you put the items comma separated.

"'Test1', 'Test2'"

Cheers,

Eduardo Jauch

Eduardo Jauch wrote:

Hi jace,

While I'm not fond of IN, you can.do exactly as you typed. 

If the list is variable, than you need a query parameter (text) with the inline sql enabled in this parameter and you pass the list on it.

IN (@items)

Where @items is your parameter and you put the items comma separated.

"'Test1', 'Test2'"

Cheers,

Eduardo Jauch

How does it look in sql ?

SELECT * 

FROM TEST
WHERE TestCode IN (@items + ')



Jace Jace wrote:

Eduardo Jauch wrote:

Hi jace,

While I'm not fond of IN, you can.do exactly as you typed. 

If the list is variable, than you need a query parameter (text) with the inline sql enabled in this parameter and you pass the list on it.

IN (@items)

Where @items is your parameter and you put the items comma separated.

"'Test1', 'Test2'"

Cheers,

Eduardo Jauch

How does it look in sql ?

SELECT * 

FROM TEST
WHERE TestCode IN (@items + ')



SELECT {yourentity}.*
FROM {yourentity}
WHERE {yourentity}.[yourfield] IN (@queryparameter)

Remeber that in advanced queries, the entity name must be between { and } and field name between [ and ].

See here: https://success.outsystems.com/Documentation/10/Developing_an_Application/Use_Data/Query_Data/SQL_Queries

and here: https://success.outsystems.com/Documentation/10/Developing_an_Application/Use_Data/Query_Data/Write_Your_Own_SQL_Queries

The parameter has to have the Expand Inline property set to True.

Cheers,
Eduardo Jauch


Eduardo Jauch wrote:

Jace Jace wrote:

Eduardo Jauch wrote:

Hi jace,

While I'm not fond of IN, you can.do exactly as you typed. 

If the list is variable, than you need a query parameter (text) with the inline sql enabled in this parameter and you pass the list on it.

IN (@items)

Where @items is your parameter and you put the items comma separated.

"'Test1', 'Test2'"

Cheers,

Eduardo Jauch

How does it look in sql ?

SELECT * 

FROM TEST
WHERE TestCode IN (@items + ')



SELECT {yourentity}.*
FROM {yourentity}
WHERE {yourentity}.[yourfield] IN (@queryparameter)

Remeber that in advanced queries, the entity name must be between { and } and field name between [ and ].

See here: https://success.outsystems.com/Documentation/10/Developing_an_Application/Use_Data/Query_Data/SQL_Queries

and here: https://success.outsystems.com/Documentation/10/Developing_an_Application/Use_Data/Query_Data/Write_Your_Own_SQL_Queries

The parameter has to have the Expand Inline property set to True.

Cheers,
Eduardo Jauch


Yes, i understand about this.

I have a list of value 

Test 1

Test 2

Test 3,

i just want to know how can build it into 'Test1', 'Test2', 'Test3'


Solution

If you have a list, you probably will have to iterate it and append in a string variable.

Something like:

ForEach

    parameter = parameter + if(list.CurrentRowNumber > 0, ",", "") + "'" + list.current.value + "'"

In the end of the ForEach, you will have the parameter like you want.

Cheers,
Eduardo Jauch

Solution