Clarity Service Management

Expand all | Collapse all

How to retrieve user_uuid to a value in a select

Jump to Best Answer
  • 1.  How to retrieve user_uuid to a value in a select

    Posted 03-15-2019 05:22 PM

    Hi! 

    Could someone help me with this?

    Env:

    CA SDM 14.1 'tus-110'

    CA Service Catalog 14.1.01.136

    SQL Server 2008

     

    I builded a object report in the catalog to retrieve the name and user id to a form in the catalog.

     

    i create 2 variables, one for the value, one for the label, in the value i put the useruuid and in the label i put the name of the person.

     

    When i retrieve the value of the select it show "undefined" like the value is not retrieving.

     

    I tried put other columns in the variable value for testing, i tested with userid, type, and last_name and it works, the values hast retrieved successfully, but when i try to retrieve useruuid it does not work.

     

    The value is showed in the report like others but i cannot retrieved with a getselectedvalue even with getselection....

    Please help!



  • 2.  Re: How to retrieve user_uuid to a value in a select

    Posted 03-17-2019 07:16 AM

    Maybe converting the column value to a string type will help.
    In Oracle , I would go for
    Select ...raw_to_hex(contact_uuid) ...
    Hope this helps
    Regards
    ....Michael



  • 3.  Re: How to retrieve user_uuid to a value in a select

    Posted 03-17-2019 06:18 PM

    for sqlserver this could be 

    contact_uuid = convert(varbinary,IIF('%contact_uuid%' = '','0x00','%contact_uuid%'),1)

     

    The more complex IIF condition is required since catalog would possibly execute that query with an empty UUID ...



  • 4.  Re: How to retrieve user_uuid to a value in a select

    Posted 03-18-2019 03:50 AM

    Good Morning Felix.

    I am not sure whether I understand your question correct.
    Still I propose to check on the below document:
    https://comm.support.ca.com/kb/How-to-get-UUID-shown-properly-in-a-catalog-formonlookupfield/kb000048450

    You face this problem because UUID is binary and this is a not supported datatype for variables in Catalog.
    The following approach resolves this.
    Use Case/Select UUID for a contact: Here convert function is used to convert binary data to integer
    table: ca_contact
    fields: userid,first_name
    query: select userid,first_name
    from ca_contact
    where CONVERT(integer,location_uuid)=%loc_uuid_int%
    Here we are converting binary UUID to integer and filtering matching records based on first select field selection.

    Kind regards, Louis.



  • 5.  Re: How to retrieve user_uuid to a value in a select

    Posted 03-18-2019 03:59 AM

    Hi Louis,

    the KD is great and will work for many cases, but a better conversion to utilize for the UUID is STRING per my suggestion above. 

    Converting to integer may result in duplicate values for two different UUIDs.
    Example:
    UUID for contact1 "0x648AF38B6A62E14F97FEE67239D5CD55" and
    UUID for contact2 "0x827194373E56BF46ADCE047F39D5CD55" will both be
    converted to integer value "970313045". 

     

    Because of that the recommendation was as follows:

     

    contact_uuid = convert(varbinary,IIF('%contact_uuid%' = '','0x00','%contact_uuid%'),1)

     

    (the more complex IIF condition is required since catalog would possibly execute that query with an empty UUID ...)

    Maybe we can extend the KD accordingly.



  • 6.  Re: How to retrieve user_uuid to a value in a select
    Best Answer

    Posted 03-18-2019 08:49 AM

    Stephanie_Maehr, my guess is that he wants to get the value selected in a form, not pass a variable to a report object. So your suggestion, even if good, does not apply here.

     

    varelafpy, Since there is no contact with an empty UUID, you do not need an IIF.

     

    You can use the CA function or SQL convert method : 

     

    CA function : select dbo.hex(contact_uuid) from ca_contact

    SQL Convert : select convert(nvarchar(32),contact_uuid,2) from ca_contact



  • 7.  Re: How to retrieve user_uuid to a value in a select

    Posted 03-18-2019 02:02 PM

    Thanks Olivier it works!

     

    I used the convert function

    "SQL Convert : select convert(nvarchar(32),contact_uuid,2) from ca_contact" added to my query and it works great.