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

[Brett Zipkin]

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?

[Grant Bruneau]

Hi Brett,

 

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

 

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

[Jason_Wolfe]

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.

[Brett Zipkin]

Sounds logical but doesn't work.  Instead I get 

 

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

 

[Brett Zipkin]

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!

[Louis van Amelsfort]

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.

[Brett Zipkin]

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?

[Grant Bruneau]

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.

 

[Brett Zipkin]

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!