topic Re: Wich fields or tables are null (oracle database) in QlikView

Wich fields or tables are null (oracle database)

Mon, 27 Apr 2015 22:00:23 GMT

i wonder if there is a way to know wich fields or tables have information in one load.

Thanks.

Re: Wich fields or tables are null (oracle database)

maxgro — Mon, 27 Apr 2015 22:08:53 GMT

what's a null table? a table without rows?

Re: Wich fields or tables are null (oracle database)

Mon, 27 Apr 2015 22:23:54 GMT

yes, without rows, only with field names.

Re: Wich fields or tables are null (oracle database)

maxgro — Mon, 27 Apr 2015 22:44:02 GMT

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;

Re: Wich fields or tables are null (oracle database)

Wed, 29 Apr 2015 15:08:36 GMT

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

Re: Wich fields or tables are null (oracle database)

maxgro — Wed, 29 Apr 2015 15:41:43 GMT

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'