SQL QUERY STORED PROCEDURE "IN" NOT WORKING

Question

Ahncel Lalu · Oct 25, 2019

When calling the below stored procedure using the management portal (Run Query) no data is being retrieve, but if it is directly executed management portal data will show.

"IN" does not work changing it to "=", "like" the stored procedure will work. Does any one know how to fix this ?

Pass values to code is 'AB','TS','SK','GM'

Query LoadData(code As %Library.String) As %SQLQuery [ SqlProc ]
{

SELECT STRING(Descrtiption,' (',Code,')') as Description,Code FROM Test.Codes
WHERE Code IN (:portCode)
}

Discussion (1)0

Robert Cemper · Oct 25, 2019

IN expects a series of values like In (1,5,23,7)

but :portCode supplies a String as 1 single value "'AB','TS','SK','GM'" which is useless

better use

SET portCode = $lb("AB","TS","SK","GM")

and

SELECT STRING(Descrtiption,' (',Code,')') as Description,Code FROM Test.Codes
WHERE Code %INLIST :portCode

0 0