Re: How to build a complex query using javascript (288 Views)
Reply
Frequent Advisor
Justin Parrott
Posts: 88
Registered: ‎03-18-2009
Message 1 of 7 (288 Views)
Accepted Solution

How to build a complex query using javascript

I need to retrieve a list of interactions in a new view that will show all interactions with related incidents that have a problem.status of "Pending Project".

I know how to do this by querying the database directly and I know how to do a basic select in javascript in Service Manager but the query is the part that confuses me. It doesn't appear to be standard SQL. The query I would like to duplicate in javascript is the following:

SELECT *
FROM INCIDENTSM1
WHERE CATEGORY <> 'purchase request' and OPEN <> 'Closed' and INCIDENT_ID in
(select SOURCE from SCRELATIONM1
WHERE DEPEND in
(SELECT "NUMBER"
FROM PROBSUMMARYM1
WHERE problem_status = 'Pending Project')
)

Any help would be appreciated. If you also know of a better way to perform this action then that would be appreciated too. Thank you.
Please use plain text.
Respected Contributor
Tommy Jensen
Posts: 307
Registered: ‎05-05-2010
Message 2 of 7 (288 Views)

Re: How to build a complex query using javascript

You cannot replicate that SQL statement exactly.

But you can do something that will return the interactions you need, just without the im id's in the actual list. Users would have to enter the interaction and look on related records tab.

In the view you put something like this in the query:

jscall("scriptlibrary.function")

in the javascript library you write your function. This function can be as complex as you like as a result it should return a string like this:

incident.id isin {"SD001","SD002"}

of course you can combine with other parameters in the view but if you are writing a javascript already then it migth as well do the main part of the select.
Please use plain text.
Frequent Advisor
SM Resource
Posts: 70
Registered: ‎05-14-2010
Message 3 of 7 (288 Views)

Re: How to build a complex query using javascript

Hi Justin,

We can achieve this one. Can you able to see the Javascript that was written.

You need to initialize these two tables and then only you can able to pull out this one.

You can write the entire SQL Statement as it is in JavaScript.

It may look like below.

Var ScQuery=new SCFile("incidents");
var query=SCQuery.doSelect("Write your query")

And also you need to use some While loop statement.

I hope this gives you some information to proceed further.

Thanks.

Please use plain text.
Frequent Advisor
Justin Parrott
Posts: 88
Registered: ‎03-18-2009
Message 4 of 7 (288 Views)

Re: How to build a complex query using javascript

Could you elaborate more? I'm looking for some real world examples and not just a one liner. Thanks for your time.
Please use plain text.
Respected Contributor
Tommy Jensen
Posts: 307
Registered: ‎05-05-2010
Message 5 of 7 (288 Views)

Re: How to build a complex query using javascript

The real world examples I have are customer property so you cannot have them and unless you pay me I do not have the time to write a specific sample just for you.
Please use plain text.
Frequent Advisor
Justin Parrott
Posts: 88
Registered: ‎03-18-2009
Message 6 of 7 (288 Views)

Re: How to build a complex query using javascript

I wasn't asking you specifically, just anyone. I didn't take the tailoring class unfortunately. I know what I need to do logically, I just don't know the syntax.

retrieve the data into a file object, convert file object to array, perform logic on array data to determine the results I need, return the results in a string formatted as the following:

incident.id isin {"SD001","SD002"}

What I don't know, is how to access the array data after I perform a convert of the object to an array. It seems that array[0] doesn't work.

Any help on the syntax would be appreciated as my company has no plans to send me to training.
Please use plain text.
Frequent Advisor
Justin Parrott
Posts: 88
Registered: ‎03-18-2009
Message 7 of 7 (288 Views)

Re: How to build a complex query using javascript

Thanks for the direction to solve this problem. I finally came up with the right syntax to make this work. Listed below is the source code to make this solution work if anyone else has a similar problem. After adding this function to a current or new script library, build a search using the following expert search query:

incident.id isin val(jscall("SomeLibrary.PendingProjectRelated"),8)


Source Code:

function PendingProjectRelated() {

// Get incident results to start query

var incidentNumber = new Array();
var incidentQueryString = 'depend=\"';
var incidentFile = new SCFile( "probsummary" );
var rc = incidentFile.doSelect( "problem.status=\"Pending Project\"" )
if ( rc == RC_SUCCESS )
{
var incidentArray = incidentFile.toArray();
incidentNumber.push(incidentArray[0]);
incidentQueryString = incidentQueryString + incidentArray[0] + '\"';

}
else
{
print( "Could not find Incident record. " + RCtoString( rc ) );
}
while(rc == RC_SUCCESS) {
rc = incidentFile.getNext();
if ( rc == RC_SUCCESS ) {
incidentArray = incidentFile.toArray();
}
else
{
// print( "Could not find Incident record. " + RCtoString( rc ) );
break;
}
incidentNumber.push(incidentArray[0]);
incidentQueryString = incidentQueryString + ' OR depend=\"' + incidentArray[0] + '\"';
}

// Use results from incident array to query the proper source id from screlation

var SCRelationSource = new Array();
var SCRelationQueryString = 'incident.id=\"';
var SCRelationFile = new SCFile("screlation");
var rc = SCRelationFile.doSelect(incidentQueryString);
if ( rc == RC_SUCCESS ) {
relate = SCRelationFile.getText();
var SCRelationArray = SCRelationFile.toArray();
SCRelationSource.push(SCRelationArray[0]);
SCRelationQueryString = SCRelationQueryString + SCRelationArray[0] + '\"';
}
else
{
print( "Could not find Incident record. " + RCtoString( rc ) );
}

while(rc == RC_SUCCESS) {
rc = SCRelationFile.getNext();
if ( rc == RC_SUCCESS ) {
SCRelationArray = SCRelationFile.toArray();
}
else
{
// print( "Could not find SCRelation record. " + RCtoString( rc ) );
break;
}
SCRelationSource.push(SCRelationArray[0]);
SCRelationQueryString = SCRelationQueryString + ' OR incident.id=\"' + SCRelationArray[0] + '\"';
}

// Use Source ID to query the proper results from the interaction File

var interactionSource = new Array();
var interactionQueryString = '\"';
var interactionFile = new SCFile("incidents");
var rc = interactionFile.doSelect(SCRelationQueryString);
if ( rc == RC_SUCCESS ) {
relate = interactionFile.getText();
var interactionArray = interactionFile.toArray();
interactionSource.push(interactionArray[0]);
interactionQueryString = interactionQueryString + interactionArray[0] + '\"';
}
else
{
print( "Could not find Incident record. " + RCtoString( rc ) );
}

while(rc == RC_SUCCESS) {
rc = interactionFile.getNext();
if ( rc == RC_SUCCESS ) {
interactionArray = interactionFile.toArray();
}
else
{
// print( "Could not find interaction record. " + RCtoString( rc ) );
break;
}
interactionSource.push(interactionArray[0]);
interactionQueryString = interactionQueryString + ',\"' + interactionArray[0] + '\"';
}
// return(interactionQueryString);
// print(interactionQueryString);
return(interactionSource);
}
Please use plain text.
The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation