SQL Requester Limitations? Not so much


OK This isn't a question, more a handy workaround.

I worked up a SQL query in the MS SQL interface on the server and it was working well (the idea was an expanded "trial kitting" report for subcontract BoMs to be able to model how much we would need to spend on materials and, based on MOQ and pack size vs materials requirements, how much "excess" stock we would be left with based on current stock holdings).

This worked perfectly in SQL but I had to use a "with PLIST as (select .... )" as an opening statement then the main query which used a left join to bring in the PLIST results as a subquery.

That did not work at all inside the SQL Requester interface. I faulted it out with our reseller who looked over my code and could not see any fixable issues (typos, syntax errors, etc. - as expected given it had worked in SQL directly). 

On a hunch, I saved the query to a Stored Procedure, with two parameters defined (the BOM product and the modelling quantity, @Param1 & @Param2).

Then, I invoked the Stored procedure within the SQL Requester (exec dbo.ProcedureName @Param1=%1%, @Param2=%2%), set up all the columns and the SQL Requester Parameter Definitions to match.

And it all worked. 

So, if you ever find that you want to run an SQL query as a Requester and the native X3 SQL Requester functionality doesn't support your particularly complex SQL, but you know the SQL works because you've tested it, you can put the SQL into a stored procedure and invoke that from the Requester.

Hope that helps


  • +1
    verified answer

    Thanks for the tip Jules.  Depending on what you need to do, you may also find creating a view and using that as the basis for a requester would work as well or better.  You cannot put SQL Requesters on menu items, which using a view also overcomes.



  • 0 in reply to Mike Shaw

    Hi Mike
    Thanks - worth mentioning that, and I've been using views for some time, particularly to be able to use a graphical requester to create menu items, landing page widgets, etc. My specific issue was that I needed to allow user-selected parameters (in this case modelling quantity) to feed back into calculations used within the Select query. 
    As far as I'm aware an SQL view can't accept external parameters, and a view within X3 can't define variables or accept parameters, (though if they can I'm all ears as to how to implement).