How to use the SET function in table transformer

Bart Hoegaerts

I'm trying to use the SET function in table transformer as described on
Using multiple SQL statements. Inserting new rows
Custom Transformation - use cases with advanced SQL queries (stiltsoft.com) 

I have created a simple table and where I want to add up 1 year per row on the date start; the added date to be set as end year.

this is the statement:
SET @count = 0,
SET @date = (SELECT T1.'Start' FROM T1),
WHILE @count < 15
BEGIN
SET @count = @count + 1,
SET @date = DATEADD(YEAR,1, @date),
INSERT INTO T1(T1.'End') VALUES (@date),
END
SELECT * FROM T1

but it returns an syntax error
SyntaxError: Parse error on line 1: SET @count = 0, SET @date -----^ Expecting 'LITERAL', 'BRALITERAL', got 'COUNT'

How do I use the SET function in table transformer? 
thanks!

Katerina Rudkovskaya _Stiltsoft_

Hi @Bart Hoegaerts,

Please note some differences between your query and the query from our documentation:



SET @cnt = 0;
SET @date = (SELECT T1.'Start' FROM T1);
WHILE @cnt < 15
BEGIN
SET @cnt = @cnt + 1;
SET @date = FORMATDATE(DATEADD(YEAR,1,@date));
INSERT INTO T1 (T1.'End') VALUES (@date)
END;
SELECT * FROM T1



Here we use @cnt, not @count because COUNT is a special function and there may happen a conflict within the query. Also pay attention on the semicolons at the end of the lines (not commas).

Also here is a variant of a slightly more complex query to get rid of an empty cell in the result table:



UPDATE T1
SET T1.'End'=FORMATDATE(DATEADD(YEAR,1,'Start')) WHERE 'Start Mark'="Start";
SET @cnt = 0;
SET @date = (SELECT T1.'End' FROM T1);
WHILE @cnt < 15
BEGIN
SET @cnt = @cnt + 1;
SET @date = FORMATDATE(DATEADD(YEAR,1,@date));
INSERT INTO T1 (T1.'End') VALUES (@date)
END;
SELECT T1.'Start', T1.'End' FROM T1


Hope it helps.