OK, in order to close this thread here is the query:
IF OBJECT_ID('tempdb.dbo.#ResultRooms', 'U') IS NOT NULL
DROP TABLE #ResultRooms; CREATE TABLE #ResultRooms (
) DECLARE @Counter INT SET @Counter = 1 WHILE (@Counter <= 20)
'INSERT INTO #ResultRooms
SELECT CAST(t_stamp AS DATE) AS DateKpi
, MAX(Room' + @Counter + ') Room
, Max(KPIA' + @Counter + ' ) AS KPIA
, Max(PlanExe' + @Counter + ') AS KPIP
, Max(KPI_Room' + @Counter + ') KPI_Room
WHERE YEAR(t_stamp)=YEAR(getdate()) AND MONTH(t_stamp)=MONTH(getdate())
GROUP BY CAST(KPI.t_stamp AS DATE)
ORDER BY CAST(KPI.t_stamp AS DATE)'
) SET @Counter = @Counter + 1
END SELECT *
I can run successfully the query on the named query test environment, but it does not work in Database query browser or Script console:
JDBC requires queries to be declared as result-returning (SELECT and friends) or status-returning (UPDATE and friends). A named query can be configured one way or the other. Script functions specify as part of the function name which form to use. The query browser has to guess which form to use, and does so with a very simplistic look into the SQL looking for SELECT. You don't have simple syntax.
You still have a SQL script, not a SQL statement, so you are just begging for trouble.