topic Sql query in QlikView

Sql query

Fri, 18 Sep 2009 13:28:14 GMT

I am trying to run a sql query in qlikview where in i am creating a temporary table #temp_table

then updating this table with different parameters and conditions

and then select * from #temp_table

script runs fine in sql but in qlikview script is running fine and no data is loaded

regards

peter

Sql query

Fri, 18 Sep 2009 13:39:34 GMT

Can you post the script you have written? Have you been successful in loading data through SQL Query before?

Sql query

Fri, 18 Sep 2009 16:59:22 GMT


[CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Initial Catalog=DATABASE;Data Source=SRV;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=PC1;Use Encryption for Data=False;Tag with column collation when possible=False]
sql
use Database
set nocount on
create table tmp_booking_leg
(book_no char(16),
carrier_id char(04),
book_date datetime,
leg_no smallint,
voyage_desc varchar(50) null,
load_port_id char(05) null,
discharge_port_id char(05) null,
etd_date datetime,
eta_date datetime,
load_agency_id char(03),
prev_eta_date datetime null,
days smallint,
maincarriage_leg_no smallint null,
maincarriage_voyage_desc varchar(50) null,
maincarriage_service_id char(04) null
)]

Sql query

Fri, 18 Sep 2009 17:04:36 GMT

this query is running fine in sql server but not in qlikview

Sql query

Fri, 18 Sep 2009 17:09:57 GMT

Hey, this is not a select query, right? You can only retrieve data from database. Making changes like insertion and creating tables is not possible from QlikView.

Sql query

Fri, 18 Sep 2009 17:30:47 GMT

but i have done this where the data is inserted in to the microsoft access database

Sql query

Fri, 18 Sep 2009 18:06:39 GMT

Even in Acess, you won't be able to do insertion. Why do you want to create a temporary table in Access. If you want, you can load the data, create a temporary table in qlikview and use it.

Sql query

Fri, 18 Sep 2009 18:10:05 GMT

how can i create a temporary table in qlikview and update that table with data from different tables

Sql query

Fri, 18 Sep 2009 19:00:39 GMT

I will just try to put a simple example here.

QVTable1:Load *;SQL Select A, B, C from table1 ;

Now you can use this table anywhere in Script using Resident:

QVTable2:Load A, B, (C+10) as DResident QVTable1;

Even operations like join, concatenate and alll are possible betweeen different tables loaded in qlikview. Refer the help document for more details.

Sql query

Fri, 18 Sep 2009 22:01:59 GMT

Why do you need to run all this in QV? I'd suggest creating a stored procedure that loads parameters and values into temps, and do the select .

Sql query

Mon, 21 Sep 2009 16:41:54 GMT

tried executing the stored procedures and it is running fine

thanks a lot

regards

peter