Clarity Service Management

Expand all | Collapse all

How to run a query with 2 wild cards on a string without treating it as a variable?

Jump to Best Answer
  • 1.  How to run a query with 2 wild cards on a string without treating it as a variable?

    Posted 02-02-2017 03:56 PM

    I'm building a report data object that runs a SQL query against the database.  Part of that query requires my WHERE clause to have something akin to    field_name like '%datavalue%'  which normally is acceptable SQL syntax.  However, when dropped into the Query box of the Data Object, I get an error like this...

     

    Error: The variable 'datavalue' has not been defined, the query may not execute properly.....

     

    Catalog uses  the percent-name-percent format to identify a variable but I really want to use it as pure sql.   How do I get around this issue so I can run the query I intend to?



  • 2.  Re: How to run a query with 2 wild cards on a string without treating it as a variable?
    Best Answer

    Posted 02-02-2017 06:30 PM

    Hi Brett,

     

    You should be able to build the where clause like this..

     

    where column like '%' + N'value' + '%'



  • 3.  Re: How to run a query with 2 wild cards on a string without treating it as a variable?

    Posted 02-03-2017 09:02 AM

    What Grant said ^^

     

    You can also execute stored procedures from a data object (i.e. exec <sp name>). So you can always create a stored procedure using whatever query you like in SQL and invoke the same from a data object.



  • 4.  Re: How to run a query with 2 wild cards on a string without treating it as a variable?

    Posted 02-03-2017 10:04 AM

    Sounds logical but doesn't work.  Instead I get 

     

    Error: The variable '' + 'datavalue' +'' has not been defined, the query may not execute properly.....

     



  • 5.  Re: How to run a query with 2 wild cards on a string without treating it as a variable?

    Posted 02-03-2017 01:14 PM

    A HA!  After working with Jason in Support I stumbled on my flaw.  I hadn't put spaces around the + signs.  So to summarize:

     

    %datavalue% - thinks it's a variable

    '%'+'datavalue'+'%'   - thinks even the pluses are part of the variable

    '%' + 'datavalue' + '%' - works.  The spaces make the difference since spaces can't be in variable names.  This treated it as 3 strings concatonated together.

     

    Thanks everybody for their feedback and assistance!



  • 6.  Re: How to run a query with 2 wild cards on a string without treating it as a variable?

    Posted 02-03-2017 03:28 AM

    Good Morning Brett.
     
    When you receive this: Error: The variable 'datavalue' has not been defined, the query may not execute properly.....
    Another possible solution could be for you to create that variable as such.
     
    Please check the below and let me know your findings.
    1. Login to the SC-UI
    2. Select administration/report builder/data objects
    3. Select ''Create Data Object' button
    4. Select ''Create Variable' button
    5. Fill in the details of the variable and hit 'Create Variable'/'OK'
     
    This would make it possible for you to then run the Data Object.
     
    Thanks and kind regards, Louis van Amelsfort.



  • 7.  Re: How to run a query with 2 wild cards on a string without treating it as a variable?

    Posted 02-03-2017 11:09 AM

    The variable method doesn't seem to work either.  If I put the wildcards in the variable string and test it, it works but when I use that report data object as the source of a drop down field, it never loads.  I'm guessing because it wants to prompt me for input and can't?



  • 8.  Re: How to run a query with 2 wild cards on a string without treating it as a variable?

    Posted 02-03-2017 11:20 AM

    Hi Brett,

     

    This stumped me a few times as well..  When you are using data objects with drop down/lookup fields you need to have an ID and Label for the data object.  Additionally, to use the data object for a Lookup field, the the _ID for the field needs to be  the ID (column) from the data object.  Here's an example data object used to pull SDM groups.

     



  • 9.  Re: How to run a query with 2 wild cards on a string without treating it as a variable?

    Posted 02-03-2017 01:11 PM

    Got it.  My simple test only had 1 column, hence broken drop down.  Adding 2 columns made it work.  But I also solved the original suggestion that failed for me.  I'll reply above on that item.  Thanks!