topic Re: Can't create table to database using ODBC in QlikView

Can't create table to database using ODBC

Mon, 18 Jan 2016 12:34:20 GMT

Hi All,

I am using qlikview to create a table in my Oracle database VIA OLEDB or ODBC -Microsoft Oracle Service .the script for creating table the table in Oracle database contains a query that querying two existing tables (Customer and Order table),The Customer table is available from the same database as where the new table will be stored while the Order table is stored from another Oracle Database that is linked to the Customer database from the Customer database server (i.e. for this connection, Customer database is the client while the Order database is the server).

and following is my SQL script

Sql

Create table Cust_Order as

select

C.Customer_ID,

C.Name,

O.Order_ID,

O.Date,

O.ProductID,

O.Amount

from

Customer_table C

left join Order_table@OrderDB O on C.Customer_iD = O.Customer_ID;

// Load the newly created table into Qlikview

CUST_ORDER:

select * from Cust_Order;

Now, my querstion is I don't know why my Qlikview returned ODBC ORA-00942 "Table Not found" error message while running the Create table statement.

Note: both Customer_table or Order_table are existed, but just in a different database.

Following is my system configuration:

Cliekt

Qlikview 11

Java 7.0

Server:

Both DB using Oracle 12G

Note the same query executed successfully from Oracle SQL Developer 4.1.1. But only failed when execute through Qlikview.

Any idea?

Thanks in advance!

Re: Can't create table to database using ODBC

Gysbert_Wassenaar — Mon, 18 Jan 2016 17:27:40 GMT

By default Qlikview cannot write to a database. If you cannot resist the urge to do silly things with the wrong tool for the job then you can enable the Open Databases in Read and Write Mode on the Settings tab in the Script Editor.

Re: Can't create table to database using ODBC

Mon, 18 Jan 2016 18:39:31 GMT

Yes, really don't understand why are you doing it with QLIK. It can't be done another way? Sometimes the mest way to solve a problem is to really go around it...

Best regards,

Carlos

Re: Can't create table to database using ODBC

maxgro — Mon, 18 Jan 2016 18:46:47 GMT

if you run in Qlik the select (no CTAS) do you get the same error or does it works?

and if it work do you really need to create a table and read the same table you've just created?

select

C.Customer_ID,

C.Name,

O.Order_ID,

O.Date,

O.ProductID,

O.Amount

from

Customer_table C

left join Order_table@OrderDB O on C.Customer_iD = O.Customer_ID;

Re: Can't create table to database using ODBC

Mon, 18 Jan 2016 23:43:58 GMT

Hi All,

Yeah I know it by Qlik is not designed to write things back to DB, and I do created a read an write connection to do this job.

The reason behind that is I need Qlikview to query multiple Oracle DB in the same query to generate it charts and reports by accepting a criteria that entered by a qlikivew user in the QVW. And the result set also need to be stored in the Oracle Database for other reasons. That is why I need to run a create table DDL query from Qlikview via ODBC before a select statement.

It was working fine when all tables sitting under the same DB. It only failed when they are stored into a different database. Anyone got any idea?

Many thanks!

Re: Can't create table to database using ODBC

Mon, 18 Jan 2016 23:45:35 GMT

Maxgro,

Thanks!. Yes it works if without the Create Table statement. However, it doesn't fulfil the business needs (see my additional response to my original post).

Re: Can't create table to database using ODBC

jagan — Tue, 19 Jan 2016 00:46:39 GMT

Hi,

Qlikview is a Dashboard tool which pulls data from the datasources and on top create the graphs on it. I am not sure why you are looking to create a new table and load the existing data and then reading the data from the new table? Instead you can directly use the query to pull the data.

What is the necessity to create the new table? To create the new table to need to do some special settings to write back to database check below link for some sample script to insert records into database table

Inserting records into Database using Qlikview Script

Regards,

Jagan.

Re: Can't create table to database using ODBC

Wed, 20 Jan 2016 06:18:17 GMT

Hi All,

After a full day working on this. I find that the issue was actually related to the stability of the connection.

I found that the reason I can't create the table Cust_Order is actually because I failed to drop the previous Cust_Order.

This means Oracle failed to execute the drop table statement that was passed from Qlikview before the create table statement.

The table was actually there before execution of create table query. If Oracle cannot drop the previous Cust_Order table, there is no way for me to create a new table with the same name.

So my work around solution is to create a store procedure that accept criterions from Qlikview and create a new Cust_Order table everytime when executed.

Than in Qlikview, everytime I reload the data, I just call the store procedure with my new criteria and follow by loading the new Cust_order table from Oracle into Qlikview.