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?
You should be able to build the where clause like this..
where column like '%' + N'value' + '%'
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.
Sounds logical but doesn't work. Instead I get
Error: The variable '' + 'datavalue' +'' has not been defined, the query may not execute properly.....
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!
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-UI2. Select administration/report builder/data objects3. Select ''Create Data Object' button4. Select ''Create Variable' button5. 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.
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?
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.
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!