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

Tue, 13 Jan 2015 13:05:52 GMT

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?

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

PrashantSangle — Tue, 13 Jan 2015 13:08:30 GMT

Hi,

Did you try with filename() and filebasename()

Regards

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

Tue, 13 Jan 2015 16:22:20 GMT

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

topic Re: How can I get all the table names from a select statement during a load script? in QlikView

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

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

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