Using Table Variables on Advanced Queries

Using Table Variables on Advanced Queries


Is there a way to use Table Variables (@tablename) inside a Advanced Query?

Because since parameters in Advanced Queries are used using that symbology (@Parameter), you can't really differentiate between them and an error will arise since you don't have a parameter definition for the table, and I haven't found anything in the forums about temporary tables or variable tables...

Are Table Variables even supported (temporary tables are, for my experience)?

Best regards,
You cannot use Records or records lists inside advance queries as input parameter,
but you get the table as a subquerie for exemple

  SELECT table.* FROM
( SELECT {table_name}.* FROM {table_name}  WHERE ....)  as table
INNER JOIN {otherTable} ON {otherTable}.[tableID]

something like that

Hello Carlos

That is not the scenario I mean, you can check what Table Variables and Temporary Variables are here

Something in the likes of:

ID int,
Name char(30))

-- Do some stuff with the table 
Yes you are right, but you cannot declare variables inside advance queries.
you can use my exemple.
in order to populate the temporary table, you need a select or something like that. if you make the select and put an alias on it, you can use the result as a temporary table but with some limitations.
I've done the following and it works:


CREATE TABLE #TableB (Data DATE, Total DECIMAL(37,8));

INSERT INTO #TableA (Data)
SELECT statement

INSERT INTO #TableB (Data, Total)
SELECT statement

SELECT statement that will create the output;


This works in an Advanced Query. I wanted to use Table Variables instead of Temporary Tables, that is it...(no drop statements required, eficiency, etc etc etc)
you can do something like this

SELECT statement that will create the output;
FROM {some table}
JOIN (SELECT statement for table a) AS tableA
  ON join condition

JOIN (SELECT statement for table b) AS TableB
  ON join condition

WHERE > @date

Hi Pedro,

That is in fact not supported due to our syntax and i suggest you to report an issue to Support.

As a workaround there is a simpler way to do exactly what you want.

DECLARE @TableB TABLE (Data DATE, Total DECIMAL(37,8));

INSERT INTO @TableB (Data, Total)
SELECT statement
SELECT statement that will create the output FROM @TableB;
Then add an input called "TableB" set it as "Expand inline" and as input pass the string "@TableB"

Anyway, as Carlos said, if possible you should try to avoid the temporary tables, as most of the times all the work can be replaced with inner queries. And with that let the sql engine to optimize the execution plans.

João Rosado
I've tried all that was possible with inner queries and cross queries, but the complexity was such that was not possible to achieve the needed outcome with them, hence the last resort solution of temporary tables

I've already considered the aproach João suggested but haven't tried yet.

I will also submit this to the Support