Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Champion III
Champion III

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;

View solution in original post

16 Replies
vishsaggi
Champion III
Champion III

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
Author

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.

sunny_talwar

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
Champion III
Champion III

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

Not applicable
Author

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

Not applicable
Author

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

vishsaggi
Champion III
Champion III

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
Author

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
Author

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.