Wich fields or tables are null (oracle database)

[#1]

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

Thanks.

[maxgro]

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

[#3]

yes, without rows, only with field names.

[maxgro · Accepted solution]

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;

[#5]

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

[maxgro]

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'