I'm writing a custom report in order to create a pick list for a Sage 50 US customer. I've defined the container for the report as a custom SQL query, copying the built-in Inventory Analysis report. I've done this because most of the information I need is already in this report. I then simplified the query to include only the data I need and added a bit to the WHERE clause so that the report can only be run for a single customer. This led me to the query you will find in the attached "PickListQuery.sql.txt" file.
However, when I try to execute the query, I get the error message in the first screenshot, "ErrorAssignment.png". I then thought I might try running the query with the Universal Engine. However, that produced the error in the second screenshot "ErrorBrackets.png".
What's strange is that if I remove ONLY the final statement in the WHERE clause (i.e. the filtering by Customer ID), the query works. I've made sure the pass through variables I'me using are the right data type, and have tried filtering by other fields in the Customers table, but all produce the same two errors. I've hit a wall and am stuck. Any suggestions? Thanks!
Replies are closed for this discussion.
I am a fool.
Since I had previously used pass-through variables for dates and the IR engine automatically performed all the formatting magic for me, I assumed the same would happen when using a pass-through for a VARCHAR argument. However, a quick glance at the SQL debug showed me that the issue was that the string literal was being dropped in the SQL without being delimited by single quotes. I simply added these quotes around the pass-through and the query executed without issue.