2 Replies Latest reply: Jan 13, 2015 11:22 AM by Jose Federico Moreno Penzo RSS

    How can I get all the table names from a select statement during a load script?

    Jose Federico Moreno Penzo

      Hi,

       

      I am trying to get all the table names from a field that contains SQL Select statements during a load script.

       

      Example:

       

      Data source fields:

       

           StatementID               //this contains a numeric value

           SQL_Statement          //this contains a string that has a SQL statement

       

      A posible value for SQL_Statement is:

       

      SELECT

           Field_1,

           Field_2,

           Field_3

      FROM

           (SELECT Field_1, Field_2, Field_ID FROM Table_1 WHERE Field_1 = 'Hello') A,

           Table_2 B,

           Table_3 C

      WHERE

           A.Field_ID = B.Field_ID

      ;

       

      What I want to obtain is the values of the names of the tables. In the example: Table_1 , Table_2 , Table_3

       

      A first approach to do this during the load script was:

       

      Load

           StatementID,

           SQL_Statement,

           TextBetween(SQL_Statement,'FROM','WHERE') as SQL_Tables

      From myDataSource;

       

      But that returns for the SQL_Tables field a value of:

       

      (SELECT Field_1, Field_2, Field_ID FROM Table_1 WHERE Field_1 = 'Hello') A,

           Table_2 B,

           Table_3 C

       

      Instead of: Table_1 , Table_2 , Table_3

       

      I am having some trouble with the sub selects on the SQL statements.

       

      I have tryied other solutions but I am still not getting the desired result.

       

      Any ideas?