Highlighted
Absent Member.
Absent Member.
559 views

Case insensitive query in Link

Jump to solution

Hi,

how can I use case insensitive queries defined in a link? What I need is that link search in device table using a query like these:

type="bizservice" and not (ucmdb.id = NULL) and istatus="In Use" and UPPER(logical.name)#"<UPPER(text contained in affected.item field)>"

I configured link for affected.item field (see attach):

$query="type=\"bizservice\" and not (ucmdb.id = NULL) and istatus=\"In Use\""
if (not null(affected.item in $File)) then $query+=(" and TO_UPPER(logical.name)#\""+toupper(affected.item in $File)+"\"")

But I receive this error:

Invalid syntax for query. Failed parsing (us.link,select.records)
type="bizservice" and not (ucmdb.id = NULL) and istatus="In Use" and TO_UPPER(logical.name)#"SERV" (us.link,select.records)
Cannot select records from link file; probable bad query: type="bizservice" and not (ucmdb.id = NULL) and istatus="In Use" and TO_UPPER(logical.name)#"SERVICE"

The problem is "TO_UPPER(logical.name)" string in query definition. I tryed to use also "ucase(logical.name)", "toupper(logical.name)" and "upper(logical.name)", but the error persists. How can I insert a uppercase condition in the query?

Best regards,

Sutot

 

0 Likes
1 Solution

Accepted Solutions
Highlighted
Absent Member.. Absent Member..
Absent Member..

Re: Case insensitive query in Link

Jump to solution

Hi Sutot,

the correct RAD function is "toupper", and it can be used also in query statements (I've just tried it)

Regards,

Ernesto

Update:
I've just tried to perform a query on ScriptLibrary table using the following query:
toupper(name) like "*EDS*"
HPSM found all records containing "EDS"/"eds"/"EdS" strings... what I noticed is that "#" is not always processed, so try using "like" 😉

View solution in original post

4 Replies
Highlighted
Absent Member.. Absent Member..
Absent Member..

Re: Case insensitive query in Link

Jump to solution

Hi Sutot,

the correct RAD function is "toupper", and it can be used also in query statements (I've just tried it)

Regards,

Ernesto

Update:
I've just tried to perform a query on ScriptLibrary table using the following query:
toupper(name) like "*EDS*"
HPSM found all records containing "EDS"/"eds"/"EdS" strings... what I noticed is that "#" is not always processed, so try using "like" 😉

View solution in original post

Highlighted
Absent Member.. Absent Member..
Absent Member..

Re: Case insensitive query in Link

Jump to solution

$query="type=\"bizservice\" and not (ucmdb.id = NULL) and istatus=\"In Use\""
if (not null(affected.item in $File)) then $query+=(" and TO_UPPER(logical.name)#\""+toupper(affected.item in $File)+"\"")

The first "logical.name" in "and TO_UPPER(logical.name)#\""+toupper(affected.item in $File)" actually is just referencing the field name in the database. Field name references in HPSM are case-sensitive..

Try::

$query="type=\"bizservice\" and not (ucmdb.id = NULL) and istatus=\"In Use\""
if (not null(affected.item in $File)) then $query+=(" and logical.name#\""+toupper(affected.item in $File)+"\"")

It can sometimes be easier if you do the conversion prior to the query:

$logicalName=toupper(affected.item in $File)
$query="type=\"bizservice\" and not (ucmdb.id = NULL) and istatus=\"In Use\""
if (not null(affected.item in $File)) then $query+=(" and logical.name#\""+$logicalName+"\"")

 

----------------------------------------------------
Kudos - what, where, how, and why
Want Good Answers? Ask Good Questions...
Highlighted
Absent Member.
Absent Member.

Re: Case insensitive query in Link

Jump to solution

Hi Ernesto,

thank you for your answer.

You're right, "toupper(logical.name)" works fine.

I had already tested it, butprobably in my test there was other errors and I didn't realize it..

Best regards,

Sutot

 

0 Likes
Highlighted
Absent Member.
Absent Member.

Re: Case insensitive query in Link

Jump to solution

Hi John,

thank you for your answer.

"upper(affected.item in $File)" was already working out, but I will test your suggestion to do the conversion prior to the query.

The problem was on logical.name. As edisanto suggested, upper(logical.name) works fine, so the right expression is:

$query="type=\"bizservice\" and not (ucmdb.id = NULL) and istatus=\"In Use\""
if (not null(affected.item in $File)) then $query+=(" and toupper(logical.name)#\""+toupper(affected.item in $File)+"\"")

Best regards,

Sutot

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.