Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

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?

1 Solution

Accepted Solutions
Not applicable

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

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

2 Replies

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

Hi,

Did you try with filename() and filebasename()

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Not applicable

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

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