Hi all,
I am trying to format a where clause that looks something like the following
Where {Name}.[FirstName] in (@Names) or @Names = ''
This is so that if the @names variable is empty it still populates the whole list.
However, if the @names = "'James', 'John'", then I get the error incorrect syntax near ','.
If i remove the ' or @Names = '' ' the code works, how can I include that line and still get the code to work?
It also works if @Names="'James'" or @Names = "'John'"
The expand inline condition is set to yes.
Thanks,
James
Hello James
Is the @Names variable of data type text? E.g. is it @Names = "James,John,james,Henry", etc?Regards,
Ângelo
Ângelo Sousa wrote:
Hi,
Yes it is. However as some of the first names have spaces each word has single quotes around it
James Timmins wrote:
I've this problem and resolved with Extend Inline (sql input property) to yes.
I think OutSystems gets a little confused when dealing with quotation marks. Funny enough, I'm dealing with a similar issue right now, as I'm sending a string with some quotation marks to a REST API, and in ServiceCenter, I see some of the quotation marks are being automatically interpreted as \ on the JSON request.
Since your code works if you remove the or @Names='' part, I would suggest check if the @Names variable is empty before you call the SQL node. If it is, you just fetch all data to populate, if not, the flow should continue to the SQL node without the or @Names = '' part. I understand it's a bit of a workaround, but it might work
Regards,
Hello James,
The reason for the error is the fact that the parameter is expanded as part of the SQL. This gives the following SQL code:
... Where {Name}.[FirstName] in ('James', 'John') or 'James', 'John' = ''
This is obviously incorrect SQL-code.
A possible solution is to include the full WHERE-clause only when @Names has a value, e.g.@NamesWhere = "Where {Name}.[FirstName] in ('James', 'John')"
You can also use a second parameters with the names that's not expanded, e.g.:Where {Name}.[FirstName] in (@NamesExpanded) or @NamesNotExpanded = ''
Please try to use like below,
Hi James,
Please check the below link as well:
https://success.outsystems.com/Documentation/Best_Practices/Development/Building_Dynamic_SQL_Statements_the_Right_Way
Manish Jawla