Variable in WHILE loop Syntax

General Discussion
1

Hi,
I try to write SQL query using variable concatenated to the table column names in while loop. Lets say I have 20 Counters from Counter1 to Counter20 and I want to write SELECT statement:

DECLARE @count INT;
SET @count = 1;
WHILE @count<= 20
BEGIN
   SELECT Counter(@count)  FROM Outputs
   SET @count = @count + 1;
END;

Of course, it throws error. What is the correct syntax in this case?

2

What does the table itself look like? Does every counter have its own column? How many rows do you expect to return?

3

This is SQL script, not a SQL statement. Only a few JDBC drivers permit SQL scripts. On top of that, it returns multiple one-row result sets. Ignition cannot handle that, either.

Consider using a recursive Common Table Expression.

2 Likes
4

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 (
    DateKpi DATE
    ,Room VARCHAR(512)
    ,KPIA REAL
    ,KPIP REAL
    ,KPI_Room REAL
    ) DECLARE @Counter INT SET @Counter = 1 WHILE (@Counter <= 20)
BEGIN
    EXEC (
            '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
    FROM KPI
    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 *
FROM #ResultRooms

I can run successfully the query on the named query test environment, but it does not work in Database query browser or Script console:

resultsKPI = system.db.runNamedQuery("Tableting_Factory1","TestRecursive", {})
resultsKPI = system.dataset.toDataSet(resultsKPI)
spreadsheet = system.dataset.dataSetToExcel(1, [resultsKPI])
filePathoutputs = "E:\\SCADA\\Tableting_Reports\\KPI_Complete_Data.xls"
system.file.writeFile(filePathoutputs, spreadsheet)

What mistake I am doing?

5

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.

1 Like