5 Replies Latest reply: Aug 15, 2016 5:49 AM by Massimo Grossi RSS

    Load Script with variables

    Christian Wylezol

      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

        • Re: Load Script with variables
          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

          • Re: Load Script with variables
            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

            • Re: Load Script with variables
              Christian Wylezol

              Hi you both,

               

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

              I do not get it working.

               

              Thanks.

              Chris

                • Re: Load Script with variables
                  Andrei Kaliahin

                  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

                • Re: Load Script with variables
                  Massimo Grossi

                  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