Having problems with your account or logging in?
A lot of changes are happening in the community right now. Some may affect you. READ MORE HERE
y4nzi Valued Contributor.
Valued Contributor.
227 views

doSelect with like statement

Hello!

Is it possible to use like within the doSelect ? I've tried this query, but it returns no rows.:

var sql = "select logical.name, ci.name from device where logical.name like \"%3109%\""

Or is there another way to replace like statement?

0 Likes
5 Replies
y4nzi Valued Contributor.
Valued Contributor.

Re: doSelect with like statement

I found that my query can be rewriten as:

var sql = "select logical.name, ci.name from device where logical.name like \"*3109*\""

But my actual issue is that I need to compare with other column of the table. How it can be done?

0 Likes
ALARMus Acclaimed Contributor.
Acclaimed Contributor.

Re: doSelect with like statement

The syntax is:

var findContact = contactFile.doSelect( "contact.name#\""+ query +"\"" );

As for your SELECT, in SM for JS:

SCFile.doSelect(“query”)
This method runs a specified query and returns a list of records that match the query's selection criteria.
The method returns an SCFile object containing the records matching the query, and a global return code value of RC_SUCCESS; or returns one of the error global return code values if the method cannot return any records.

So Query for doSelect - give you a record(s) with your criteria and after that your can use any fields from this records.

For your case (for first found record only):

var query = "3109";
var DevTable = SCFiles("device"); var findCI = DevTable.doSelect("logical.name#\""+ query +"\"");
// next line another way to use in doSelect without "\
// var findCI = DevTable.doSelect('logical.name#"'+ query +'"');
if ( findCI == RC_SUCCESS )
{
print("Found record with Logical.name="+DevTable.logical_name+"; and ci.name="+DevTable.ci_name);
}

If need all records:

var i=0;
var query = "3109"; var DevTable = SCFiles("device"); var findCI = DevTable.doSelect("logical.name#\""+ query +"\""); // next line another way to use in doSelect without "\ // var findCI = DevTable.doSelect('logical.name#"'+ query +'"'); if ( findCI == RC_SUCCESS ) {
do
{
++i; print(i+" -> Found record with Logical.name="+DevTable.logical_name+"; and ci.name="+DevTable.ci_name); }
while (findCI.getNext() == RC_SUCCESS);
}

 Try and tell me what you got.

0 Likes
y4nzi Valued Contributor.
Valued Contributor.

Re: doSelect with like statement

ALARMus,

Thank you for your reply and time!

Using variable will not help me. I have just simplified my query, I need to do comparison with another column of other table in th query. My actual code looks like:

var varA = "someValue"
var fDev = new SCFile("device", SCFILE_READONLY);
var sql = "
	select 
		d2.logical.name, d2.ci.name, dt.device.name, d2.subtype 	
	from 
		device d1 
		join cirelationship c on (d1.logical.name = c.logical.name) 
		join device d2 on (c.relationship.name = d2.logical.name) 
		join devtype dt on (d2.type = dt.device.type) 
		join history h on (h.object.id = d1.logical.name)
	where 
		d1.contact.name = \"" + varA + "\""
		and h.object.name = 'device'
		and h.action = 'add'
		and h.description like '%' + d2.logical.name + '%'"
var rc = fDev.doSelect(sql);
if (rc == RC_SUCCESS){
	...
}

I am having troubles with last condition. I don't know correct syntax

0 Likes
ALARMus Acclaimed Contributor.
Acclaimed Contributor.

Re: doSelect with like statement

As i know JS in SM  do not use MSSQL requests.

And as for me - your sql variable is a mix of SQL(select-from-where construction) and JS-SM language (like this - \"").

0 Likes
Highlighted
Acclaimed Contributor.. brav0 Acclaimed Contributor..
Acclaimed Contributor..

Re: doSelect with like statement

how about having the like statement as below

 

h.description like % + "\"+d2.logical.name+"\" %"

I am Listening..
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.