%CONTAINS parameters in embedded SQL
What is a correct way to pass parameters to the %CONTAINS in embedded SQL statement when searching thru %Text property?
s sp="child,health"
&SQL(
DECLARE c1 CURSOR FOR
SELECT ID INTO :id FROM ICD WHERE Name %CONTAINS (:sp)
...
is not the same as executing the following in the SQL Manager
SELECT ID FROM ICD WHERE Name %CONTAINS ('child','health')
Comments
Alexander
I think you need to use 2 parameters in your COS code
s sp1="child"
s sp2="health"
&SQL(
DECLARE c1 CURSOR FOR
SELECT ID INTO :id FROM ICD WHERE Name %CONTAINS (:sp1, :sp2)
This would be equivalent to what you are doing in the Portal.
Brendan
Thank you Brendan.
If this is the only way, then I will have to do a dynamic SQL instead of the embedded one because the number of search words changes from one query to the next.
That's fine - dynamic SQL has only a small overhead in comparison to embedded SQL.
If you need free-text search, might I recommend iFind? Quick article here:
https://community.intersystems.com/post/free-text-search-way-search-you…
you do need an iKnow enabled license, but this should work on %Text fields.
Thank you Brendan.
If this is the only way, then I will have to do a dynamic SQL instead of the embedded one because the number of search words changes from one query to the next.