I checked several links in outsystems Documentation and Forum but I didn't find the answer to my problem.
I want to use a dynamic query "where EMP_ID in @usernames"
1) If I try to use @usernames as Text and set the "Enable Inline" option to True, I still need to add quotations similar to this question which is not fully answered
https://www.outsystems.com/forums/discussion/78211/how-to-pass-comma-separated-value-to-advance-sql-query
2) When I want to use @usernames as Text List in the BuildSafe_InClauseTextList action, it gives me error "Invalid Data Type. The same element data type required. Expected 'TextLiteral Record' instead of 'Text'."
Based on this link, the list type should be RecordList of TextLiteral.
https://success.outsystems.com/documentation/11/reference/outsystems_apis/sanitization_api/#BuildSafe_InClauseTextList
3) The answer in the link https://www.outsystems.com/forums/discussion/78877/converting-text-to-textliteral/ provides example of manual input of TextLiteral. But in my case, I loop through an aggregate (from external database) and save values (which are of type Text) in the @usernames.
So my question now, how to convert Text to TextLiteral knowing that the variables looped over are of type "Text"?
I am stuck on this part. Any idea how to achieve this?
Hello again Maitha,
I did mention one way to convert your text list into Text literals, I will develop that idea further now.
I will attach an oml with this code so you can see if it fits your use case, you can find the Server Action under the Server Actions Tab named TextToTextLiteral.
Hope it helps!
Paulo Rosário
Hi, in outsystems documentation it's mentioned that we can build temp table for this dynamic values, can you explain how that can be done?
Anyways we need to pass the list of values to the SQL query, I didn't get how we can create temp table without passing list to the SQL.
Hello Maitha,
Just by reading your post, it looks like you are comparing EMP_ID to a list of usernames that are text.
Are these EMP_ID texts as well? Do they contain values that are the same as Usernames in order to filter them?
To use the IN statement in SQL you must compare the same type of variable.
For example :
SELECT {Person}.*
FROM {Person}
WHERE {Person}.[Id] IN ( @IdList )
This will return a list of all the Persons that have their ID in the IdList which is a list of Ids of the Person entity.
Maybe in your case, you need to compare the EMP_ID to the employee via their username.
You can use IN with a SELECT to get your employees via their usernames.
WHERE {Person}.[Id] IN (
SELECT {Person}.[Id]
WHere {Person}.[Name] IN ( @NameList ) )
This will return the Person ID form the Persons with their name in the NameList.
To convert text into a text record list you can use the AppendAll action for example, wich you can find in the System dependencies.
Hi Paulo,
"Are these EMP_ID texts as well? Do they contain values that are the same as Usernames in order to filter them? "
Yes the EMP_ID value in one table is the same as Username in another view. But because using joins over linked server is causing time out error.
So we thought of reading data in a different way as an alternative to the join.
We enabled cashing, indexing, minimized the data retrieved in the view but the performance is still very slow.
So now i want to pick only the data from the view then I i will retrieve the data from the other table where the EMP_ID values in @usernames list.
I want to convert from "Text" to "TextLiteral" type. How can i do that?
any ideas?
Hi Maitha Khanji,
Can you check this link and already have solution.
https://www.outsystems.com/forums/discussion/78877/converting-text-to-textliteral/
Thanks,
Ramesh
Thank you so much Paulo! it is working now!
Glad I could help :)