Qlik Community

QlikView Integration

Discussion Board for collaboration on QlikView Integration.

Not applicable

List of Tables

Hi,

maybe someone of you can help me.

We have a database where I take Datas from.

We take this datafields out of tables from the database.

Now my question:

I dont Need datas from a table but a list of all tables within the database.

How can I realise that?

Our IT-Guy wrote to me the following, but unfortunately qlikview cannot handle this:

 

select

substr(segment_name,1,30) segment,

substr(segment_type,1,10) typ,

bytes/1024/1024 MB

from dba_segments

where segment_type = ‘TABLE’

Thanks in advance

Chris

1 Solution

Accepted Solutions
flipside
Valued Contributor II

Re: List of Tables

Try this ...

Table1:

Load *;
SQLTABLES;

qualify *;
Table2:
Load * resident Table1 where TABLE_TYPE='TABLE';
unqualify *;

drop table Table1;

10 Replies
MVP
MVP

Re: List of Tables

MVP & Luminary
MVP & Luminary

Re: List of Tables

Qlikview can send that same SQL statement to the database server. Provided you connect with the correct credentials the database server will execute that statement and return the results to Qlikview. Qlikview doesn't do anything with/to the SQL apart from sending it to the database server for execution. If it doesn't work then either your dba made an error or the credentials you use to connect to the database don't have enough priviliges the execute the sql statement. Ask your dba for help in that case.


talk is cheap, supply exceeds demand

Re: List of Tables

If this works for your database (what RDBMS type is this?) you can submit it in exactly the same way by opening a connection to the database and submitting the same statement with 'SQL' in front (and terminated with a semicolon)

Peter

flipside
Valued Contributor II

Re: List of Tables

Check out the SQLTABLES and SQLCOLUMNS commands in Qlikview help, they should give you all the tables in the database you have connected to.

flipside

Re: List of Tables

Which database you are using?

Not applicable

Re: List of Tables

Hi Kush141087,

we are using Oracle Database 9.

Chris

Not applicable

Re: List of Tables

Hi Flipside,

I tried your Suggestion and it works fine.

I just Need to Filter to some Special tables, but this unfortunatley dont work yet.

Can you see the mistake?

sqltables
WHERE TABLE_SCHEMA = 'CODA'
AND TABLE_TYPE = 'Table'
AND TABLE_NAME like 'EFA'

Thanks in advance

Chris

flipside
Valued Contributor II

Re: List of Tables

These commands can be a little tricky to get right. It should also be noted that the fields & data returned may be different even between different connection providers (i.e. ODBC or OLEDB) to the same database.

From memory, you might need to do a preceding load something like ...

**connection string **

Load *

where TABLE_SCHEMA ='CODA' AND TABLE_TYPE='Table' AND Wildmatch(TABLE_NAME,'*EFA*');

sqltables;

flipside

edit: No this doesn't work, my memory is obviously not as good as I thought! Use Gysbert's method.

Loading SQLTABLES into a named table might show as an error but will still run. You can do a preceding statement Load *; to get rid of the error red underline, so ...

TableName:

Load *;

SQLTABLES;

Highlighted
flipside
Valued Contributor II

Re: List of Tables

You might need to dump the table to a txt file and read back in. I'm sure there's a way to avoid this but don't have it to hand  ...

Table1:

Load *;
SQLTABLES;

store Table1 into Table1.txt (txt);

DROP Table Table1;

Table2:
LOAD * FROM [Table1.txt] (txt, utf8, embedded labels, delimiter is ',', msq)
where TABLE_TYPE='TABLE';

flipside