topic Re: Use of exists in the SQL in statement in QlikView

Use of exists in the SQL in statement

qlikviewforum — Thu, 11 Sep 2014 06:54:11 GMT

I have field called f_Acct which contains around 1000 values before loading the FACT table from a database.

Load *,

where exists(f_Acct,ACCT);

SQL Select Column1,

Column2,

ACCT

From Account_Table;

Instead of using the exists in the Qlikview load statement I want make use of the same in the SQL Statement. As the SQL load all the data from the Account_Table and then we filters the values in the Preceding load using exists which having performance issue. Please help!

Re: Use of exists in the SQL in statement

tresB — Thu, 11 Sep 2014 06:59:33 GMT

Try like:

Load * ;

SQL Select Distinct Column1,

Column2,

ACCT

From Account_Table;

Re: Use of exists in the SQL in statement

Thu, 11 Sep 2014 07:01:06 GMT

Why don't you create a QVD for Account_Table and then use it?

Thanks,

Singh

Re: Use of exists in the SQL in statement

AbhijitBansode — Thu, 11 Sep 2014 07:03:19 GMT

write the sql Select statement in the same syntax which you use on database.

SQL Select statements will be excuted by database engine not by QlkView.

SQL Select statements are send to the database by QlikView for excution and result s returned in hte QlikView.

Hence all the SQL syntax would work in QlikView SQL Statements.

Re: Use of exists in the SQL in statement

qlikviewforum — Thu, 11 Sep 2014 07:04:07 GMT

This may not improve the performance as I have huge no of records which is coming from SQL. So I want to make use of where clause in the SQL statement instead of QV load statement.

Re: Use of exists in the SQL in statement

AbhijitBansode — Thu, 11 Sep 2014 07:09:41 GMT

Load

Sql Select * from YourTable where Field1='Yourfilter';

Re: Use of exists in the SQL in statement

tresB — Thu, 11 Sep 2014 07:13:16 GMT

I have advised to use distinct in SQL Select part, that means it gets executed in the source database and NOT in QV.

Re: Use of exists in the SQL in statement

Thu, 11 Sep 2014 07:13:33 GMT

you can make a coma separated list variable of the ID's in f_Acct using concat functions and then pass it in SQL statement

Load

Sql Select * from YourTable where Field1 in ($(vList));

Re: Use of exists in the SQL in statement

AbhijitBansode — Thu, 11 Sep 2014 07:21:55 GMT

Hi tresesco, your answer is 100% correct, I didn't mean to contradict your answer, I thought its better to give explanation of what you've specified.

Re: Use of exists in the SQL in statement

qlikviewforum — Thu, 11 Sep 2014 13:44:39 GMT

Hi,

I tried doing that but as the string is too long in the ($(vList)) variable, oracle is throwing the error("string literal too long"). I think I need to split the string into half then try doing it into using two IN clause in the SQL.

Any suggestions on how to achieve this?

Re: Use of exists in the SQL in statement

qlikviewforum — Thu, 11 Sep 2014 13:58:14 GMT

If I pass the variable in the SQL without single quotes($(vList)) then it is not fetching any records. If I pass the single quotes ('$(vList)') then it is throwing error. Can you please help?

Re: Use of exists in the SQL in statement

anbu1984 — Thu, 11 Sep 2014 14:05:14 GMT

Error("string literal too long" means your string is more than 4000 chars. It seems all the accounts are clubbed into one string. Check the value of $(vList)

Re: Use of exists in the SQL in statement

qlikviewforum — Thu, 11 Sep 2014 14:09:22 GMT

It contains all the values with comma seperated. This is what should be passed into the SQL IN clause right?

vList='xyz','trt','rte','ete'

By the way should I pass single quotes while passing the variable in the SQL or not?

Re: Use of exists in the SQL in statement

anbu1984 — Thu, 11 Sep 2014 14:10:46 GMT

Yes

Re: Use of exists in the SQL in statement

Thu, 11 Sep 2014 14:12:30 GMT

Hello,

You can try using exists() function instead of IN.

Load

select f_Acct_orig from

YourTable i1

join

(select 408007421 as f_Acct union all

select 407039314 union all

select 409691748 union all

select 407741572 union all

select 605662536 union all

select 407773540 union all

select 407759193) i2 on i1.f_Acct_orig = i2.f_Acct

The best way to do this is as above. Transform your id's into select .... union all format...and then keep the variable with that value..and use like above.

Thanks.

Angad

Re: Use of exists in the SQL in statement

qlikviewforum — Thu, 11 Sep 2014 14:13:41 GMT

If I pass single quotes I am getting the below error. Please suggest

"string literal too long"

Re: Use of exists in the SQL in statement

anbu1984 — Thu, 11 Sep 2014 14:24:31 GMT

Try with few accounts say less than 10 in vList and check if you get the result.