How to declare a table variable when designing a custom query?

Hi guys,

  I have a SQL scripts with "declare table variable" clause that runs in Microsoft SQL Server. When I try the same query in Spotfire, it gives me an error. I'm wondering what can I do to fix this?

SQL query:

  declare @myCaleTable Table

  (asdate datetime)

  insert into @myCaleTable 

  select distinct as_date from [dbo].[My_Metric_V] where ....

  select top 1 asdate from @myCaleTable where....

When copied into custom query designer, getting the following error: 

  External error:

  Incorrect syntax near the keyword 'declare'.

  Incorrect syntax near ')'.

(1) Answer