IN Query for list of string

Back to Forums

Jace Jace

Solved

Question

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?

30 Nov 2017

Eduardo Jauch

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

See solution in context

30 Nov 2017

Eduardo Jauch

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

30 Nov 2017

3 replies

Last reply 30 Nov 2017

Show thread

Hide thread

Jace Jace

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

30 Nov 2017

Eduardo Jauch

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

30 Nov 2017

Jace Jace

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'

30 Nov 2017

IQ78

hi Jace,

is it the same one? https://www.outsystems.com/forums/discussion/30747/how-to-loop-in-aggregate-similar-to-sql-in-clause/

regards,

30 Nov 2017

Eduardo Jauch

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

30 Nov 2017

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.

See the full guidelines