DECLARE and SET not working on VDB

[adi22]

We are executing below on VDB: DECLARE @date DateTime SET @date = (SELECT CURRENT UTC TIMESTAMP) SET @date = dateadd(dd,-5, @date) Error Message: [Code: , SQL State: 50000] Remote org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered "[*]SET[*] @date =" at line 1, column 1. Was expecting: "alter" | "begin" | "call" | "create" | "delete" | "drop" | "exec" | "execute" | "insert" | "merge" ...

[shawkins]

You are using SQL Server syntax which is not directly supported.  You can use Teiid procedure syntax in a procedure block to do something similar.

[adi22]

Hi Steven,

Please guide us on how to write a Teiid procedure for DECLARE AND SET

[shawkins]

You can do it all as part of declare:

BEGIN
  DECLARE TIMESTAMP myTs = timestampadd(sql_tsi_day, -5, now());
   ...
END

Or in a procedure assignment does not require a keyword:

BEGIN
  DECLARE TIMESTAMP myTs;
  myTs = timestampadd(sql_tsi_day, -5, now());
  ...
END