Load Script with variables

[#1]

Hi,

I have a database with many tables. I started to load the list of sqltables and filtered them.

Now I want to load in the script many tables at once without scriptcode for every single table.

After Loading I want to store them in a Directory as csv.

For that I want to create a variable, that gives me back the Table_SCHEMA-entry (CODA)

Additionally I wanted to create a second variable (varTableName), that give me the TableName-entry (efaEFA_ADOCTMP, EFA_ASSET and so on)

I tried this to load automatically:

LET DirDataQVD  = 'F:\CODA\';

Tmp_sqltables:
Load *;
SQLTABLES;

sqltables:
Load
TABLE_SCHEMA,
TABLE_TYPE,
TABLE_NAME
resident Tmp_sqltables
where TABLE_SCHEMA = 'CODA' AND TABLE_TYPE = 'TABLE' AND TABLE_NAME like 'EFA*' ;
drop table Tmp_sqltables;

Let vTableName = TABLE_NAME;
Let vTableScheme = TABLE_SCHEMA;

// ------------------------------------------------------------------------------------------

$(vTableName):
SQL SELECT *
FROM $(varTableName).$(varTableName);

store $(vTableScheme) into $(DirDataQVD)$(vTableName).csv (txt, delimiter is ';');
drop Table $(vTableName);

But unfortunately it is not working
(TABLE_NAME.TABLE_NAME is wrong, correct would be FROM CODA.EFA_ASSET)

How can I realise this? (please see attached my file)

very much thanks in advance.

Chris

[Gysbert_Wassenaar]

You need to use a loop to iterate through the Temp_sqltables table and for each row use the peek function to retrieve the field values and store them in the variables.

You can find an example here that does the same with excel sheets, which are also tables retrieved with the SQLTABLES command: Re: Loading Multiple Excel Files and Multiple Excel sheets

[sunny_talwar]

I think you will need to use Peek() function to store the value of your table_name and table_schema into a variable. You cannot just do LET vVar = table_name

[#4]

Hi you both,

that sounds good. Would you please help me with that?

I do not get it working.

Thanks.

Chris

[crusader_]

Hi Christian,

First of all looking into your script and error message you have a typo.

You need to put varTableSchema instead of varTableName in below statement.

$(vTableName):
SQL SELECT *
FROM $(varTableName).$(varTableName);

Fix it and try other suggestions with loop and Peek() function.

Hope this helps.

Andrei

[maxgro]

ODBC CONNECT...........

LET DirDataQVD  = '.';          // use your folder

Tmp_sqltables:

Load *;

SQLTABLES;

sqltables:

Load

TABLE_SCHEM,

TABLE_TYPE,

TABLE_NAME,

TABLE_CAT

resident Tmp_sqltables

where TABLE_CAT = 'sisim_stat' AND TABLE_TYPE = 'TABLE' AND TABLE_NAME like 'd*'

;          // replace with your schema, table_name

drop table Tmp_sqltables;

for i=0 to NoOfRows('sqltables')-1

  Let vTableName = peek('TABLE_NAME', $(i), 'sqltables');

  Let vTableCat = peek('TABLE_CAT', $(i), 'sqltables');

  Let vTableScheme = peek('TABLE_SCHEM', $(i), 'sqltables');

  trace vTableName=$(vTableName);          // for test

  trace vTableCat=$(vTableCat);

  trace vTableScheme=$(vTableScheme);

  $(vTableName):

  SQL SELECT top 10 *         // remove top 10

  FROM $(vTableCat).$(vTableScheme).$(vTableName);

  store $(vTableName) into [$(DirDataQVD)\$(vTableName).csv] (txt, delimiter is ';');

  drop Table $(vTableName);

NEXT