Extraction Loop

[neerajthakur]

Hi All, 

I have many sections in which I am using below extraction code, each section have different schema names and tables listed of that schema.

NoConcatenate
ListofTable:
SELECT table_name As TableName,
table_schema,
table_type
FROM information_schema.tables
where table_schema='non_license'
and table_name in
(
'inspection_details',
'inspection_details_documents'
);

for i=0 to NoOfRows ('ListofTable')-1

let vTableName=Peek('TableName',$(i),'ListofTable');
//let vTableName1="&vTableName&";

$(vTableName):
SQL SELECT * from
"xxx"."non_license".$(vTableName) ;
Store $(vTableName) Into [lib://Extraction (analytics_xxxx) (xxxxx)/xxx/non_license/$(vTableName).qvd] (qvd);

Drop Table $(vTableName);

next

Using it like this and reloading the app from QMC its failing as it might be executing different section after next statement how can I automate this to work more efficiently.

PS: Its working when loading each section separately.

[Clever_Anjos]

" its failing" what kind of failure?

[neerajthakur]

Let Case be :

Section1 contain following:

Schema: Schema1

Table Name : Table1,Table2

Section2 contain following:

Schema: Schema2

Table Name : Table3,Table4

now after next it executes as Schema1.Table3 not found

It should function as all the table in Section1 should load and drop first before starting section2 and for further sections.

[Clever_Anjos]

Could you give a try with this?

NoConcatenate
ListofTable:
SELECT
table_name As TableName,
table_schema,
table_type,
rowno() as index
FROM information_schema.tables
where table_schema='non_license'
and table_name in
(
'inspection_details',
'inspection_details_documents'
);

for i=1 to NoOfRows ('ListofTable')
t:load TableName, table_schema resident ListofTable where index = $(i);
vTableName=Peek('TableName');
vSchemaName=Peek('table_schema');
drop table t;

$(vTableName):
SQL SELECT * from
"xxx"."$(vSchemaName)".$(vTableName) ;
Store $(vTableName) Into [lib://Extraction (analytics_xxxx) (xxxxx)/xxx/non_license/$(vTableName).qvd] (qvd);

Drop Table $(vTableName);

next

[neerajthakur]

rowno() will not work and I have observed sometimes for some schemas 'TableName' work and sometime 'tablename' in Let vTableName ==Peek('tablename',$(i),'ListofTable'); expression

I have some similar tables name but in different schemas. I am extracting like this for example Schema non_license and authentication folder location in Extraction\non_license and Extraction\authentication and for public and all other schemas into Extraction folder directly can we overcome this.

[Clever_Anjos]

fixing the rowno() issue

NoConcatenate
ListofTable:

Load *, rowno() as index;
SELECT
table_name As TableName,
table_schema,
table_type
FROM information_schema.tables
where table_schema='non_license'
and table_name in
(
'inspection_details',
'inspection_details_documents'
);

for i=1 to NoOfRows ('ListofTable')
t:load TableName, table_schema resident ListofTable where index = $(i);
vTableName=Peek('TableName');
vSchemaName=Peek('table_schema');
drop table t;

$(vTableName):
SQL SELECT * from
"xxx"."$(vSchemaName)".$(vTableName) ;
Store $(vTableName) Into [lib://Extraction (analytics_xxxx) (xxxxx)/xxx/non_license/$(vTableName).qvd] (qvd);

Drop Table $(vTableName);

next