topic Re: Where Clause with ODBC Connection in QlikView

Where Clause with ODBC Connection

Tue, 22 Nov 2016 19:44:45 GMT

I am using a where clause to filter data that is loaded via an ODBC connection. I've tested the same where clause in an alternative qvw document which reads from a csv file and the syntax works without error. Why doesn't the syntax work when there is an ODBC connection.

WHERE ([ENTITY_STATUS] ='A' AND [ORIGINAL_EFF_DATE]<=20151031) OR ([ENTITY_STATUS] ='T' AND [ORIGINAL_EFF_DATE]<=20151031 AND [TERMIN_DATE]>20161031);

Re: Where Clause with ODBC Connection

vishsaggi — Tue, 22 Nov 2016 19:51:38 GMT

Can you put your ODBC connection outside your table name like: Check if this works?

ODBC CONNECT32 TO ADGDTAPR (XUserId is SLIMISJMPbYCDRD, XPassword is RTCIbHJOBTbOWSNOSBMA);

//-------- Start Multiple Select Statements ------

T1_ACTIVE:

SQL SELECT ACCOUNT_NO,

BASE_PREMIUM,

CUSTOMER_NO,

ENTITY_STATUS,

ORIGINAL_EFF_DATE,

POLICY_EFF_DATE,

POLICY_TYPE,

PROFESSION_FLAG,

SOURCE_CODE,

STATE_CODE,

STUDENT_FLAG,

TERMIN_DATE

FROM S40TREV.ADGDTAPR.NSOCVGP

WHERE ([ENTITY_STATUS] ='A' AND [ORIGINAL_EFF_DATE]<=20151031) OR

([ENTITY_STATUS] ='T' AND [ORIGINAL_EFF_DATE]<=20151031 AND [TERMIN_DATE]>20161031);

Re: Where Clause with ODBC Connection

Tue, 22 Nov 2016 19:54:39 GMT

I tested your script with the ODBC outside the table name and it did not work. The ODBC works inside the table name when there is no WHERE clause.

Re: Where Clause with ODBC Connection

sunny_talwar — Tue, 22 Nov 2016 19:55:11 GMT

Does this query works in Toad or another query tool?

SQL SELECT ACCOUNT_NO,

BASE_PREMIUM,

CUSTOMER_NO,

ENTITY_STATUS,

ORIGINAL_EFF_DATE,

POLICY_EFF_DATE,

POLICY_TYPE,

PROFESSION_FLAG,

SOURCE_CODE,

STATE_CODE,

STUDENT_FLAG,

TERMIN_DATE

FROM S40TREV.ADGDTAPR.NSOCVGP

WHERE ([ENTITY_STATUS] ='A' AND [ORIGINAL_EFF_DATE]<=20151031) OR ([ENTITY_STATUS] ='T' AND [ORIGINAL_EFF_DATE]<=20151031 AND [TERMIN_DATE]>20161031);

Re: Where Clause with ODBC Connection

vishsaggi — Tue, 22 Nov 2016 19:55:37 GMT

What is the error you are getting ? Can you send me a screen shot ?

Re: Where Clause with ODBC Connection

Tue, 22 Nov 2016 20:01:29 GMT

Here's the error, which is also in a file atttacment.

Re: Where Clause with ODBC Connection

Tue, 22 Nov 2016 20:02:37 GMT

Yes this query works in another query tool. It works in Cognos Impromptu, AS400 query tools.

Re: Where Clause with ODBC Connection

vishsaggi — Tue, 22 Nov 2016 20:06:43 GMT

There is an access violation i can see in the error. Can you try running this script in debug mode?

Like:

ODBC CONNECT32 TO ADGDTAPR (XUserId is SLIMISJMPbYCDRD, XPassword is RTCIbHJOBTbOWSNOSBMA);

Table1:

FIRST 100 LOAD *;

SQL SELECT ACCOUNT_NO,

BASE_PREMIUM,

CUSTOMER_NO,

ENTITY_STATUS,

ORIGINAL_EFF_DATE,

POLICY_EFF_DATE,

POLICY_TYPE,

PROFESSION_FLAG,

SOURCE_CODE,

STATE_CODE,

STUDENT_FLAG,

TERMIN_DATE

FROM S40TREV.ADGDTAPR.NSOCVGP

WHERE ([ENTITY_STATUS] ='A' AND [ORIGINAL_EFF_DATE]<=20151031) OR ([ENTITY_STATUS] ='T' AND [ORIGINAL_EFF_DATE]<=20151031 AND [TERMIN_DATE]>20161031);

Re: Where Clause with ODBC Connection

Tue, 22 Nov 2016 20:12:07 GMT

Here's the script copied from Cognos Impromptu, the syntax works in this software.

where T1.ENTITY_STATUS = 'A' and T1.ORIGINAL_EFF_DATE < 20151101 or T1.ENTITY_STATUS = 'T' and T1.ORIGINAL_EFF_DATE < 20151101 and T1.TERMIN_DATE > 20161031

Re: Where Clause with ODBC Connection

Tue, 22 Nov 2016 20:18:14 GMT

I copied your script exactly, ran in debug, got same error. I've tested my script before with a very simple WHERE clause and it worked fine, so I know it is something in the WHERE clause that is causing problem.

Re: Where Clause with ODBC Connection

Tue, 22 Nov 2016 20:26:09 GMT

I retested the simple WHERE clause and found that it only works when loading from csv, but does not work when loading from ODBC. This makes me think that there may be security restrictions that allow me to load data, but not filter the data, but I'm still not certain.

Re: Where Clause with ODBC Connection

vishsaggi — Tue, 22 Nov 2016 20:28:45 GMT

Comment the script and just try to connect to ODBC and see. Like comment the SQL script and just debug using the ODBC Connection.

Re: Where Clause with ODBC Connection

Tue, 22 Nov 2016 20:33:27 GMT

I just did exactly what you recommended. I commented the WHERE clause and ran the script. The ODBC connection works perfectly, the data is loading. If I then uncomment the WHERE clause the script fails when it reads the WHERE clause. As noted in my prior reply, there may be security restrictions that allow loading data, but not filtering, but I'm not sure this is the cause because I can filter in other softwares.

Re: Where Clause with ODBC Connection

vishsaggi — Tue, 22 Nov 2016 20:37:39 GMT

Can you do this in the load script and see what you get like:

T1_ACTIVE:

LOAD *

Where(

([ENTITY_STATUS] ='A' AND [ORIGINAL_EFF_DATE]<= '20151031')

([ENTITY_STATUS] ='T' AND [ORIGINAL_EFF_DATE]<= '20151031' AND [TERMIN_DATE] > '20161031')

);

SQL SELECT

ACCOUNT_NO,

BASE_PREMIUM,

CUSTOMER_NO,

ENTITY_STATUS,

ORIGINAL_EFF_DATE,

POLICY_EFF_DATE,

POLICY_TYPE,

PROFESSION_FLAG,

SOURCE_CODE,

STATE_CODE,

STUDENT_FLAG,

TERMIN_DATE

FROM S40TREV.ADGDTAPR.NSOCVGP;

Re: Where Clause with ODBC Connection

Tue, 22 Nov 2016 20:58:01 GMT

Your syntax worked, but I want to understand all your changes.

1. You loaded all fields first.

2. You enclosed the entire WHERE clause in parenthesis

3. You made no changes to the SQL SELECT

It works perfect, but don't WHERE clauses typically follow FROM clauses ?

Re: Where Clause with ODBC Connection

vishsaggi — Tue, 22 Nov 2016 21:10:38 GMT

I am not sure why it was giving access violation issues with the SQL script, so thought would pull all the data first into QV and then do the condition @ QV side.

Your syntax worked, but I want to understand all your changes.

1. You loaded all fields first.

Thought would pull all the data first into QV as we are getting some issues @ the SQL level.

2. You enclosed the entire WHERE clause in parenthesis

This is just a way we write if we are using OR's and AND's to make it more consistent. Not hard fast rule here.

3. You made no changes to the SQL SELECT

Yes, I am telling QV to read all the data from SQL table.

It works perfect, but don't WHERE clauses typically follow FROM clauses ?

In Qlikview i am telling to load the table with WHERE condition, we are doing a preceding load so we do not need any FROM by default QV understands it.

Re: Where Clause with ODBC Connection

Tue, 22 Nov 2016 21:13:51 GMT

Thanks for all your help !!