DQ Query Reader SET Migration

[Edlueze]

Can I still use “SET” in my SQL statements in the new DB Query Reader?

I generated all my (many!) SQL statements using scripts that ended up looking something like this:

SET @NodeName = 'Input Product Array';
SET @NodeScenario = 'Actual';
SET @FocusClient = $${SFocusClient}$$;
SET @FocusReportDate = $${SFocusReportDate}$$;

SELECT 
	ReportDate AS ReportDate,
	FocusProduct,
	Description AS Description,
	Price AS Price,
	Cost AS Cost,
	Quantity AS Quantity,
	Share AS Share,
	Revenue AS Revenue,
	Profit AS Profit
FROM simProductArray
WHERE FocusClient=@FocusClient
	AND DATE_FORMAT(ReportDate, '%Y-%m-%d;%H:%i:%s.%f')=@FocusReportDate
	AND NodeName=@NodeName
	AND NodeScenario=@NodeScenario 
	AND Product = FocusProduct
ORDER BY Revenue DESC

I can’t remember why, but the use of the “SET” statements was critical in my script. Unfortunately the new DB Query Readers hate “SET” (I think because the nodes wrap an additional SELECT around the SQL statement). Looks like I am going to have to manually change them all anyway, but is there a way to keep the “SET” (or an equivalent) such that all my WHERE variables remain at the top of the SQL statement?

[tobias.koetter]

Hello Edlueze,
the old Database Reader node did split-up the text into several statements based on “;” and executes all statements except the last one first and then executes the last statement as query. Since this was some magic in the background which caused all sorts of problems the new new DB Query Reader sends all the text as a single statement to the database and expects a data table as result of this call. However you should be able to execute all the SET statements via the DB SQL Executor node right in front of the DB Query Reader node. This node also allows you to enable multiple statement execution via the “Support multiple SQL statements” options in the node dialog.
Bye
Tobias