Trying to parse multi select list to (SQL in) clause

I have tried looking through this form to find the answer, todate I have not had any luck finding anything that suits my needs, or I have not adequatly described my situation in the search option, apologies for that.
 
I am trying to get multiple items into my IN cluase in the where statement, I am using Eclipse Version: 3.7.1.
 
This is in the script -> open section.
 
var where = ""; 
if (params["CREWID"].value) 
where = " W.CREWID IN ('" + params["CREWID"].value + "')";
 
The values I am getting from Maximo look like this =CREW1,=CREW2, I would really like to be able to format the output to be ('CREW1','CREW2'). If I manually change the list output in Maximo to be CREW1','CREW2 it works. I omit the first and last single quotes as they are already included in the where = " W.CREWID IN ('" + params["CREWID"].value + "')"; statement.
 
Thanks in advance
Jag

Find more posts tagged with

Comments

wwilliams

try using MXReportSqlFormat.createParamWhereClause("w.crewid", params["CREWID"])

JagannathaSuta

Thanks for the reply wwilliams, but there is no context around how or where to use this.
 
I am not sure that this would resolve my situation.
 
I need the values that are uiput into CREWID to be formatted from =CREWID1, =CREWID2 to CREWID1','CREWID2
 
the values are picked up from the Maximo list box, as there is no indication that the function you have so kindly offered me, does not look like it is formatting the string to the format that is usable in the query.

wwilliams

<a data-ipb='nomediaparse' href='http://www-01.ibm.com/support/docview.wss?uid=swg21605970'>http://www-01.ibm.com/support/docview.wss?uid=swg21605970</a>

JagannathaSuta

Hhhmmm, not sure ow that applies to what I have set up.
 
the query I have built is looking for the values in the in clause to be in('CREWID1','CREWID2')
I do not relish the idea of declaring all 100+ CREWID's in the open script section.
 
Is there no way to parse the current in(=CREWID1,=CREWID2) in to "in('CREWID1','CREWID2) 
As the document you pointed me to indicates the way to deal with multiselect parameters is to declare them, this I am not to keen on as there may be anywhere from 1 - 100+ CREWID's selected at run time.

wwilliams

MXReportSqlFormat.createParamWhereClause("person.displayname", params["user"]);
 
equates to
 
((person.displayname = 'ZZ1234') or (person.displayname = 'ZZWILSON'))
 
If I entered in the parameter =ZZ1234,=ZZWILSON

wwilliams

this isn't pretty, but it appears to work
<pre class="_prettyXprint _lang-js">
myWhere = new String();
var temp = new Array()
var myString = params["crew"] ;
temp =myString.split(",");

for (var i=0;i< myString.split(",").length; i++ ) {
myWhere = myWhere + "'" + temp[i] +"',";

}
myWhere = myWhere.replace(/,\s*$/,"");
myWhere = " w.crewid in ("+ myWhere + ")"
</pre>
Parameter Fred,Tom,Lou
Fri Jun 23 13:58:16 CDT 2017 DEBUG >>> myWhere: w.crewid in ('Fred','Tom','Lou')

JagannathaSuta

Nearly there, awesome piece of code.
 
Result from your code:
 
AND w.crewid in ('=APS','=APSCARP')
 
How can we no remove the =
 
Thanks for your persistence and patience :-)

wwilliams

var myString = params["crew"].replace(/=/g,'') ;
 
by the way
Parameter =Fred,=Tom,=Lou
scriptLogger.debug(" MxWhere " + MXReportSqlFormat.createParamWhereClause("w.crewid", params["crew"]))
 
results in
MxWhere ((w.crewid = 'Fred') or (w.crewid = 'Tom') or (w.crewid = 'Lou'))
 
so tell me again why it wouldn't work for you

JagannathaSuta

Hi wwilliams, 
With the fisrt bit of code you kindly provided, it was able to format the Maximo parameter string from =CREWID1,=CREWID2 to be
Where crewid in ('=CREWID1','=CREWID2')
But I have the code set up to use the "IN" statement, using where ((w.crewid = 'CREW1') or (w.crew = 'CREWID2')) may not work as there may be dozens of selections made.
 
However I have managed to resolve the issue with thanks to you for supplying me with the code.
 
With a little bit of tweaking I have manage to get the results I was after.
 
var myWhere = new String(); 
var temp = new Array() 
var myString = params["CREWID"] ; 
temp = myString.split(","); 
for (var i=0;i< myString.split(",").length; i++ ) 
{ 
myWhere = myWhere + "'" + temp +"',"; 
} 
myWhere = myWhere.replace(/=/g,""); 
myWhere = myWhere.replace(/,\s*$/,""); 
myWhere = " w.crewid in ("+ myWhere + ")"
 
This acheives what I need.
 
Thank you.