Where Clause with ODBC Connection

[#1]

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);

[#2]

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.

[vishsaggi]

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

[#4]

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.

[vishsaggi · Accepted solution]

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')

   OR

   ([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;

[#6]

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 ?

[vishsaggi]

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.

[#8]

Thanks for all your help !!