web stats
Need to pass delimited string for IN clause - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 10-08-2019, 07:55 AM
clarksss clarksss is offline
OBX.2 Kenobi
 
Join Date: Jul 2013
Posts: 75
clarksss is on a distinguished road
Exclamation Need to pass delimited string for IN clause

Here is my Mirth Code:
Code:
var si = dbConn.executeCachedQuery("EXEC Profile.pListSpecialIndicators NULL, NULL, '" + ruleBuilderProfileLocations + "', '" + lastTimePolledDateTime + "'");
Here is the WHERE clause using IN, where this is failing:
Code:
WHERE reg.Location_MisLocID in (@RuleBuilderProfileLocations)
the ruleBuilderProfileLocations is a comma delimited string that looks like this:

'HLSIDHS','KWHINNJ','HJIIKLL','XODIMSL','KWHINNJ', 'HJIIKLL','KWHINNJ','HJIIKLL','KWHINNJ','HJIIKLL', 'KWHINNJ','HJIIKLL','KWHINNJ','HJIIKLL','KWHINNJ', 'HJIIKLL','KWHINNJ','HJIIKLL','KWHINNJ','HJIIKLL', 'KWHINNJ','HJIIKLL','KWHINNJ','HJIIKLL','KWHINNJ', 'HJIIKLL','KWHINNJ','HJIIKLL'

I have tried passing this string over as a parameter in the stored procedure, using double quotes, but get this error:

The identifier that starts with 'HLSIDHS','HJIIKLL','XODIMSL','KWHINNJ','HJIIKLL', 'KWHINNJ','HJIIKLL','KWHINNJ','HJIIKLL','KWHINNJ', 'HJIIKLL','KWHINNJ','HJIIKLL','KWHINNJ','HJIIKLL', 'KWHINNJ','HJIIKLL','KWHINNJ','HJIIKLL' is too long. Maximum length is 128.

I have tried removing the quotes from each delimited item and passing that over as one string like this:
'HLSIDHS'HJIIKLL,XODIMSL,KWHINNJ,HJIIKLL,.....'
but that didn't seem to work.

I was hoping someone has tried to do this before and found a solution.
let me know if this is doable... I think it should be, and maybe I am over thinking things.

thanks in advance.

Last edited by clarksss; 10-08-2019 at 08:08 AM.
Reply With Quote
  #2  
Old 10-08-2019, 08:22 AM
cory_cole cory_cole is offline
Mirth Guru
 
Join Date: Mar 2012
Posts: 1,277
cory_cole is on a distinguished road
Default

Try putting () around RuleBuilderProfileLocations
Reply With Quote
  #3  
Old 10-08-2019, 08:51 AM
clarksss clarksss is offline
OBX.2 Kenobi
 
Join Date: Jul 2013
Posts: 75
clarksss is on a distinguished road
Default My Work

that did not seem to work either, assuming I put them in the correct places... do you have an example by any chance (in case I am doing it wrong)??
Reply With Quote
  #4  
Old 10-08-2019, 09:00 AM
cory_cole cory_cole is offline
Mirth Guru
 
Join Date: Mar 2012
Posts: 1,277
cory_cole is on a distinguished road
Default

I don't. The problem that you are having is that your sp is taking the commas and considering them as different parameters. Try this...

Create an array and push the items on. In your sp you may have to parse through the array.
Reply With Quote
  #5  
Old 10-08-2019, 09:03 AM
clarksss clarksss is offline
OBX.2 Kenobi
 
Join Date: Jul 2013
Posts: 75
clarksss is on a distinguished road
Default

yah I could do that too.. i will give that a go thanks
Reply With Quote
  #6  
Old 10-08-2019, 10:23 AM
clarksss clarksss is offline
OBX.2 Kenobi
 
Join Date: Jul 2013
Posts: 75
clarksss is on a distinguished road
Thumbs up Solution --

I kept the Mirth code the same,
but in my stored procedure the code was changed to this:

Code:
 WHERE reg.Location_MisLocID IN (SELECT value FROM STRING_SPLIT(@RuleBuilderProfileLocations, ','))
thanks to everyone who had ideas.
Reply With Quote
Reply

Tags
delimited, executecachedquery, in clause, string

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -8. The time now is 09:03 PM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2019, vBulletin Solutions, Inc.
Mirth Corporation