Subscribe to this thread
Home - General / All posts - table name as variable in a query component using multiple SQL queries
vincent

1,919 post(s)
#29-Jul-20 15:08

Hi,

I have around 15 queries to run on a single table. I know that I can pack all queries in one query component and trigger them all at the same time. But I don't know how to declare the name of the table as a query parameter. That was not possible in M8 if I remember well.

Is it possible in 9 ? How ? Thank you !

Example query :

--SQL 

Update [20060300] SET [date_time]

CAST ( [date_concat] as datetime)

vincent

1,919 post(s)
#29-Jul-20 19:15

I tried this, but it does not work :

--SQL 9

VALUE @theTable2Process nvarchar = '[20060801]';

--#1

ALTER TABLE @theTable2Process 

(

Rename [LATITUDE N/S] [LATITUDE] ,

Rename [LONGITUDE E/W] [LONGITUDE]

);

--#2

ALTER TABLE @theTable2Process (

ADD [mfd_id] INT64,

ADD INDEX [mfd_id_x] BTREE ([mfd_id])

);

Any way of doing this ?

Should I script the query text like in M8 ?

LeRepère
138 post(s)
#29-Jul-20 19:29

Vincent

Try Value @t TABLE = [Table]

vincent

1,919 post(s)
#29-Jul-20 19:33

Already tested I think. I'll test again.

vincent

1,919 post(s)
#29-Jul-20 19:36

No luck with these :

--Sql 9

> VALUE @t TABLE = [20060801];

ALTER TABLE @t 

(

Rename [LATITUDE N/S] [LATITUDE] ,

Rename [LONGITUDE E/W] [LONGITUDE]

);

Cannot parse query.

--

--

> VALUE @t TABLE = '[20060801]';

ALTER TABLE @t 

(

Rename [LATITUDE N/S] [LATITUDE] ,

Rename [LONGITUDE E/W] [LONGITUDE]

);

Cannot parse query.

tjhb

9,320 post(s)
#29-Jul-20 20:39

AFAIK, stored values or parameters can't be used to stand for tables in queries that create tables or change table schemas--that is, CREATE, ALTER, DROP--but only in SELECT, INSERT, or UPDATE.

The principle is that the query engine must be able to compile the whole query (or command sequence) before it begins execution.

It can't do that in cases where tables either do not exist at the start of the code, or will change within it.

Actually from memory, it is more subtle. A CREATE statement must fail, for the reason just given. In practice, DROP <component> also answers itself.

The subtlety is that each time a table is ALTERed (explicitly, without using a stored value or parameter), any previous references persist but continue to use the previous schema. This affects SELECT, INSERT and UPDATE queries as well--so care should be taken to recreate references to tables after they are changed.

I need to check these comments, please bear that in mind.

Manifold User Community Use Agreement Copyright (C) 2007-2019 Manifold Software Limited. All rights reserved.