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

Jules