I use quite a lot of SQL in my workflow, yet somehow never discovered this one before. It turns out that if you're using SQL to pull data back for a Set Fields action, it must begin with the SELECT keyword, or it won't return any results.
For example, if you have a Set Fields with this SQL: *SELECT name* *FROM arschema* it'll work fine. But if you insert a comment before it: *-- Comment* *SELECT name* *FROM arschema* or even */* Comment */ SELECT name* *FROM arschema* the Set Fields will operate as if "No Request Match" (i.e. it'll display the No Match error, or set the target fields to NULL, depending upon how you've got it configured). What's interesting here is that the SQL in these queries is syntactically correct and they're submitted to the database by ARS without any error. If you submit the SQL manually (through SQL Plus or SQL Server Management Studio, etc), it works correctly and returns the expected data. Apparently, though, Remedy doesn't know how to deal with it if it doesn't begin with the keyword SELECT. I only just discovered this because I was attempting to use a query containing a WITH clause in SQL Server to create a Common Table Expression to flatten out a recursive data structure. Using the WITH clause, which MUST be first in the query (and can't be contained in a subquery) is the only way to do this in a single query. Of course, the work-around is to create a view containing the CTE, which is what I ultimately had to do. It's just a less convenient solution. Anyway, just something interesting I just discovered. -charlie _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "Where the Answers Are, and have been for 20 years"
