topic Urgent: How to use result set from one SQL , use it as a variable and use it in different query in QlikView

Urgent: How to use result set from one SQL , use it as a variable and use it in different query

Wed, 16 Nov 2011 05:25:00 GMT

I want value of a field from one table and use this value for creating another table. I've used the following script but the variable is not getting interpolated.

ODBC CONNECT TO database (XUserId is NDJeQRdNHbXEWRRMfaYKHRVMfC, XPassword is LQAJdBVGVBOGTZcNHTAB);
SQL create table abc.qlikview_max_dt as (select max(login_dt) as max_dt from abc.sample1) with data ;
ValueFromSQL1: select max_dt from d_cog_t.qlikview_max_dt;
Table1: Load max_dt as aa resident ValueFromSQL1;

set max_dt_val=fieldvalue('aa',0,Table1);
SQL drop table abc.qlikview_max_dt;

SQL create table abc.newTable as (select log_user_id,login_dt,touch_ti from abc.sample1

where login_dt = $(max_dt_val)) with data;

I'm able to establish connection to database and create abc.qlikview_max_dt table.I'm also able to drop the mentioned table.
However, when I assign fieldvalue('aa',0,Table1) to max_dt_val variable, its value is not getting expanded in SQL. I tried using peek function also. This is the error I get:

SQL Error:[Teradata][ODBC Teradata Driver][Teradata Database] Syntax error: expected something between '(' and the string 'a' keyword.
SQL Scriptline:
SQL State:37000
select * from abc.sample1 where login_dt=fieldvalue('aa',0,Table1)

Please help me,as it is very urgent.

Urgent: How to use result set from one SQL , use it as a variable and use it in different query

Wed, 16 Nov 2011 05:45:41 GMT

Instead of SET use LET. Also check it the the SQL date data type will match with qv variable. If not format the variable in SQL.

Regards,

Kiran.

Urgent: How to use result set from one SQL , use it as a variable and use it in different query

Wed, 16 Nov 2011 07:26:22 GMT

let var= peek(' aa',0,tablename);

hope it will work.

try it.

regards

Urgent: How to use result set from one SQL , use it as a variable and use it in different query

Wed, 16 Nov 2011 07:33:21 GMT

Thanks Kiran,

I've changed set to let and though there are no syntax errors seen, the table abc.newTable gets created without any records.

SQL select max(login_dt) as max_dt_val from abc.sample1;

SQL drop table abc.newTable;

SQL create table abc.newTable as (select log_user_id,login_dt,touch_ti from abc.sample1

where login_dt = $(max_dt_val)) with data;

NewTable: select * from d_cog_t.newTable;

Load * resident NewTable;

The table newTable does not contain any records. However if I use a subquery instead of max_dt_val ie

create table abc.newTable as (select log_user_id,login_dt,touch_ti from abc.sample1

where login_dt =(select max(login_dt) as max_dt from abc.sample1)) with data;

, the table newTable contains records which is the exact output.

Urgent: How to use result set from one SQL , use it as a variable and use it in different query

Wed, 16 Nov 2011 07:46:38 GMT

You can enable the show Generate Log files in Document Properties -> General. When you reload it will track the script executed and you can have a look at the run time script. There must be some issue with the data formatting, which can be found thru log file.

Sub query is not a good practise if the data size is heavy. Besides variable can be reusable all throught. So lets try resolving with variable.

Regards,

Kiran.

Urgent: How to use result set from one SQL , use it as a variable and use it in different query

Wed, 16 Nov 2011 09:19:48 GMT

Thanks all for your replies,

Indeed there was an error with formatting.Since max_dt_val is a date field, it should be in quotes.

In place of

SQL create table abc.newTable as (select log_user_id,login_dt,touch_ti from abc.sample1

where login_dt = $(max_dt_val)) with data;

I used

SQL create table abc.newTable as (select log_user_id,login_dt,touch_ti from abc.sample1

where login_dt = '$(max_dt_val)') with data;

Now it is working fine.

Urgent: How to use result set from one SQL , use it as a variable and use it in different query

Wed, 16 Nov 2011 09:29:00 GMT

One more thing that I want to do is to schedule this entire script to run every day.

Please tell me how to do that.

Urgent: How to use result set from one SQL , use it as a variable and use it in different query

Wed, 16 Nov 2011 10:46:43 GMT

You can run a batch file using windows scheduler:

"C:\Program Files\QlikView\qv.exe /r C:\testDocument.qvw"

Other way is to use document scheduler (I never used this) which is mentioned in reference manuel

Kiran.

Urgent: How to use result set from one SQL , use it as a variable and use it in different query

Wed, 16 Nov 2011 11:33:23 GMT

Hi Kiran,

Can you please tell where should

"C:\Program Files\QlikView\qv.exe /r

C:\testDocument.qvw"

be written.

Urgent: How to use result set from one SQL , use it as a variable and use it in different query

Wed, 16 Nov 2011 15:56:22 GMT

Open notepad type it and save it as QVRELOAD.bat. You can use this in windows scheduler.

Kiran.