Hi everyone,
I am building a multi-select filter in OutSystems and sanitizing my selected IDs into this format:
"N'452',N'451'"
When I use it in my Advanced SQL like this:
WHERE ( @ExportIds = '' OR {Requests}.[Id] IN (@ExportIds) )
Database returned the following error:
An expression of non-boolean type specified in a context where a condition is expected, near ','.
It works when only one value is present, but fails when multiple values exist. How can I correctly use a sanitized multi-value ID list in an IN clause in OutSystems Advanced SQL?
Hello,
You have to set the Expand Inline to YES.
Regards
As Paulo mentioned, you may have forgotten to change the input to Expand Inline.Adding just one small note about the overall flow: what you should have done by now is create an input list of type TextLiteral List (e.g" N'452',N'451' "), then pass it to BuildSafe_InClauseTextList. After that, split the result using String_Split with a comma (,) as the separator, and finally pass the output to your query of course after assigning Expand Inline to true.
@Sherif El-Habibi I think its the ExportIds, should use BuildSafe_InClauseIntegerList instead
I think if he’s using values like N'452', N'451', then they should be treated as Text. If he were using something like 1, 2, 3, 4, then yes, that would be Integer.
And by the way, I made a mistake earlier we don’t need String_Split here. The action already returns the values comma-separated.
Hi Bitragunta Gnanarjun Reddy,
as Paulo wrote, you need to set the Expand Inline setting to Yes. You should also sanitize the list of ids using the BuildSafe_InClauseIntegerList actions to prevent SQL injection (see Sanitization API for O11 or ODC).
This action is not returning an empty string when the list is empty but an IN-clause with a single default value ("0" for empty Integer List and "''" for empty Text List). So your condition @ExportIds = '' will never evaluate to True. I normally just add two parameters to my Advanced SQL for IN clauses:
The conditional WHERE clause then is:
WHERE ( @HasExportIds = 0 OR {Requests}.[Id] IN (@ExportIdLiterals) )
I hope this helps.
I agree with all comments above, when use Where ... In... in SQL u must use 2 point:
1. BuildSafe_InClauseIntegerList() or BuildSafe_InClauseTextList()
2. Set Expand Inline = Yes for ListIds text