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

[#1]

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?

[PrashantSangle]

Hi,

Did you try with filename() and filebasename()

Regards

[#3 · Accepted solution]

Managed to solve it with variations of the following:

'|'&SubField(upper(SelectSentence),'FROM') as FROMs

...

if(SubStringCount(FROMs,'WHERE')>=1,TextBetween(FROMs,'|','WHERE'),

        if(SubStringCount(FROMs,')')>=1,TextBetween(FROMs,'|',')'),

            if(SubStringCount(FROMs,';')>=1,TextBetween(FROMs,'|',';')))) as SQLTables

...

subfield(SQLTables,',') as SQLTables