Case insensitive query in Link

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

 

  • Verified Answer

    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" ;)

  • $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 "\"")

     

  • 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

     

  • 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