CA Service Management

 View Only
  • 1.  Service Catalog Data Object Query

    Posted Jun 28, 2022 05:23 AM
    Edited by Markus Hergeth Jun 28, 2022 05:32 AM
    Hi,
    is it possible to create a Dataobject with an "IN" SQL query?
    I have no problem with:
    select * from table where serialnumber = '%VARIABLE%'  -> VARIABLE is A111 or B222 or C333
    select * from table where serialnumber IN ('A111','B222','C333') -> also delivers the correct result
    but when I try
    select * from table where serialnumber IN %VARIABLE% -> VARIABLE is ('A111','B222','C333') 
    or
    select * from table where serialnumber IN ('%VARIABLE%') -> VARIABLE is A111','B222','C333 
    I get no error but also no results

    But this query works:
    select * from table where serialnumber IN ('%VARIABLE%') -> VARIABLE is A111 or B222 or C333

    so the problem seems to be the comma or the quote in the variable.

    Any ideas if this should work?
    Or is there another way to create a dynamic SQL query and use this in the dataobject?

    Thanks and best regards,
    Markus


  • 2.  RE: Service Catalog Data Object Query

    Posted Jun 28, 2022 09:43 AM
    Hello, 

    select * from table where serialnumber IN ('%VARIABLE%') -> VARIABLE is A111','B222','C333

    It does not work because %VARIABLE% is a string, not a list, you need a list for use "IN" clause in a select.
     
    Dynamic sentences would work, you need to build the sencence in a VARCHAR then execute.. but something easier would be:

    Option 1:
    declare @List table (value nvarchar(8))
    insert into @list(value) values('A1111')
    insert into @list(value) values('A1111')
    insert into @list(value) values('A1111')

    select * from table where serialnumber IN (select value from @List) 

    Option 2: (It depends upon your SQL Servers version and compatibility level (>=SQL Server 2016 /  Compat Level 130): 

    select * from table where serialnumber IN (select value from split_string(%VARIABLE%,',')) 


    I did not try them but I am pretty sure it wpould work with minor corrections...

    Hope this helps.
    Jorge





    ​​


  • 3.  RE: Service Catalog Data Object Query

    Posted Jun 28, 2022 10:24 AM
    Hi Jorge,

    thanks, you brought me on the right track!

    I work with an Oracle Database so the correct syntax is

    ....where serialnumber IN (SELECT regexp_substr('A111,B222,C333','[^,]+',1,LEVEL) as serialnuber from dual
    CONNECT BY regexp_substr('A111,B222,C333','[^,]+',1,LEVEL) IS NOT NULL)

    And now I can pass the string into the variable of the dataobject.

    Cheers, Markus