Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
i wonder if there is a way to know wich fields or tables have information in one load.
Thanks.
if you want to know the tables with 0 records in an Oracle db, something like that
but it can take a lot of time
ODBC CONNECT32 TO [xxxxx;DBQ=xxx] (XUserId is xxxxx, XPassword is xxxx); |
OracleTableList:
SQL
select TABLE_NAME, OWNER from all_tables
where owner='DWHCONT'; // change the owner/schema
Let vTableCount = NoOfRows('OracleTableList');
For i = 0 To $(vTableCount) -1
LET vMyTableName = Peek('OWNER', $(i), 'OracleTableList') & '.' & Peek('TABLE_NAME', $(i), 'OracleTableList');
TableNumRows:
SQL SELECT '$(vMyTableName)' as TABLE_NAME, count(*) as NUM_ROWS
FROM $(vMyTableName)
where ROWNUM <=1 ;
Next i;
if you want to know the number of records of the loaded (in QlikView) tables
for i=0 to NoOfTables()-1
let t=TableName($(i));
tt: load '$(t)' as TableName, NoOfRows('$(t)') as TableRows AutoGenerate 1;
next;
what's a null table? a table without rows?
yes, without rows, only with field names.
if you want to know the tables with 0 records in an Oracle db, something like that
but it can take a lot of time
ODBC CONNECT32 TO [xxxxx;DBQ=xxx] (XUserId is xxxxx, XPassword is xxxx); |
OracleTableList:
SQL
select TABLE_NAME, OWNER from all_tables
where owner='DWHCONT'; // change the owner/schema
Let vTableCount = NoOfRows('OracleTableList');
For i = 0 To $(vTableCount) -1
LET vMyTableName = Peek('OWNER', $(i), 'OracleTableList') & '.' & Peek('TABLE_NAME', $(i), 'OracleTableList');
TableNumRows:
SQL SELECT '$(vMyTableName)' as TABLE_NAME, count(*) as NUM_ROWS
FROM $(vMyTableName)
where ROWNUM <=1 ;
Next i;
if you want to know the number of records of the loaded (in QlikView) tables
for i=0 to NoOfTables()-1
let t=TableName($(i));
tt: load '$(t)' as TableName, NoOfRows('$(t)') as TableRows AutoGenerate 1;
next;
Massimo I have a problem, i tried to make a loop to load all the tables but when I load every owner loads it again. I mean, for example:
ABM
ABM,AHL
ABM,AHL, ARL
ABM, AHL, ARL, AS
How could i fix the script for only charge 1 time each owner?
ODBC CONNECT32 TO [xxxxx;DBQ=xxx] (XUserId is xxxxx, XPassword is xxxx); |
Owner:
LOAD
Owner
FROM
(ooxml, embedded labels, table is Hoja1);
for j = 0 to NoOfRows('Owner')-1;
let NoR = NoOfRows('Owner');
let PR = peek('Owner',$(j),'Owner');
OracleTableList:
SQL
select TABLE_NAME, OWNER from all_tables
where owner= '$(PR)'; // change the owner/schema
Let vTableCount = NoOfRows('OracleTableList');
For i = 0 To $(vTableCount) -1
LET vMyTableName = Peek('OWNER', $(i), 'OracleTableList') & '.' & Peek('TABLE_NAME', $(i), 'OracleTableList');
tablasconinfo:
SQL SELECT '$(vMyTableName)' as TABLE_NAME, count(*) as NUM_ROWS
FROM $(vMyTableName)
where ROWNUM <=1 ;
Next i;
next j
maybe a distinct from excel?
anyway, I tried this and it seems to works
// just 3 schema to test
OracleOwnerList:
SQL select distinct OWNER from all_tables where owner in ('SYS', 'SYSTEM', 'DBSNMP');
for j = 0 to NoOfRows('OracleOwnerList')-1;
let NoR = NoOfRows('OracleOwnerList');
let PR = peek('OWNER',$(j),'OracleOwnerList');
trace PR='$(PR)'; // trace to test
OracleTableList:
SQL
select TABLE_NAME, OWNER from all_tables where owner='$(PR)';
Let vTableCount = NoOfRows('OracleTableList');
//Let vTableCountQVD = 'dbo.[' & NoOfRows('OracleTableList') & ']';
// 10 to test
For i = 0 To 10// $(vTableCount) -1
LET vMyTableName = Peek('OWNER', $(i), 'OracleTableList') & '.' & Peek('TABLE_NAME', $(i), 'OracleTableList');
LET vMyTableNameQVD = Replace(Peek('TABLE_NAME', $(i), 'OracleTableList'), ' ', '_');
trace vMyTableName='$(vMyTableName)'; // tace to test
// comment to test
// $(vMyTableNameQVD):
// SQL SELECT * FROM $(vMyTableName) ;
// STORE $(vMyTableNameQVD) INTO $(ExtractQVD)$(vMyTableNameQVD).QVD;
// DROP Table $(vMyTableNameQVD);
Next i;
NEXT j;
log is
Connected
OracleOwnerList << all_tables 3 lines fetched
PR='SYSTEM'
OracleTableList << all_tables 153 lines fetched
vMyTableName='SYSTEM.LOGMNR_PARAMETER$'
vMyTableName='SYSTEM.LOGMNR_SESSION$'
vMyTableName='SYSTEM.MVIEW$_ADV_WORKLOAD'
vMyTableName='SYSTEM.MVIEW$_ADV_BASETABLE'
vMyTableName='SYSTEM.MVIEW$_ADV_SQLDEPEND'
vMyTableName='SYSTEM.MVIEW$_ADV_PRETTY'
vMyTableName='SYSTEM.MVIEW$_ADV_TEMP'
vMyTableName='SYSTEM.MVIEW$_ADV_FILTER'
vMyTableName='SYSTEM.MVIEW$_ADV_LOG'
vMyTableName='SYSTEM.MVIEW$_ADV_FILTERINSTANCE'
vMyTableName='SYSTEM.MVIEW$_ADV_LEVEL'
PR='DBSNMP'
OracleTableList << all_tables 178 lines fetched
vMyTableName='SYSTEM.LOGMNR_PARAMETER$'
vMyTableName='SYSTEM.LOGMNR_SESSION$'
vMyTableName='SYSTEM.MVIEW$_ADV_WORKLOAD'
vMyTableName='SYSTEM.MVIEW$_ADV_BASETABLE'
vMyTableName='SYSTEM.MVIEW$_ADV_SQLDEPEND'
vMyTableName='SYSTEM.MVIEW$_ADV_PRETTY'
vMyTableName='SYSTEM.MVIEW$_ADV_TEMP'
vMyTableName='SYSTEM.MVIEW$_ADV_FILTER'
vMyTableName='SYSTEM.MVIEW$_ADV_LOG'
vMyTableName='SYSTEM.MVIEW$_ADV_FILTERINSTANCE'
vMyTableName='SYSTEM.MVIEW$_ADV_LEVEL'
PR='SYS'
OracleTableList << all_tables 1.101 lines fetched
vMyTableName='SYSTEM.LOGMNR_PARAMETER$'
vMyTableName='SYSTEM.LOGMNR_SESSION$'
vMyTableName='SYSTEM.MVIEW$_ADV_WORKLOAD'
vMyTableName='SYSTEM.MVIEW$_ADV_BASETABLE'
vMyTableName='SYSTEM.MVIEW$_ADV_SQLDEPEND'
vMyTableName='SYSTEM.MVIEW$_ADV_PRETTY'
vMyTableName='SYSTEM.MVIEW$_ADV_TEMP'
vMyTableName='SYSTEM.MVIEW$_ADV_FILTER'
vMyTableName='SYSTEM.MVIEW$_ADV_LOG'
vMyTableName='SYSTEM.MVIEW$_ADV_FILTERINSTANCE'
vMyTableName='SYSTEM.MVIEW$_ADV_LEVEL'