Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Where Clause with ODBC Connection

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

1 Solution

Accepted Solutions
vishsaggi
Esteemed Contributor III

Re: Where Clause with ODBC Connection

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;

16 Replies
vishsaggi
Esteemed Contributor III

Re: Where Clause with ODBC Connection

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

Not applicable

Re: Where Clause with ODBC Connection

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

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

vishsaggi
Esteemed Contributor III

Re: Where Clause with ODBC Connection

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

Not applicable

Re: Where Clause with ODBC Connection

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

Not applicable

Re: Where Clause with ODBC Connection

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

vishsaggi
Esteemed Contributor III

Re: Where Clause with ODBC Connection

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

Not applicable

Re: Where Clause with ODBC Connection

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

Not applicable

Re: Where Clause with ODBC Connection

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.