%CONTAINS parameters in embedded SQL

Question

Question

Alexander Grishkan · May 9, 2017

#ObjectScript #Caché

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

Discussion (4)0

Log in or sign up to continue

Brendan Bannon · May 9, 2017

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

0 0

score 0 · Answer 1 · 2017-05-09T13:22:42-04:00

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.

score 0 · Answer 2 · 2017-05-10T07:08:32-04:00

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-your...

you do need an iKnow enabled license, but this should work on %Text fields.

score 0 · Answer 3 · 2017-05-09T13:21:11-04:00

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.