Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
Hi,
Did you try with filename() and filebasename()
Regards
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