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.

[Bart Hoegaerts]

Perfect ! this works well ! Thanks a lot !