Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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);
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;
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);
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.
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);
What is the error you are getting ? Can you send me a screen shot ?
Here's the error, which is also in a file atttacment.
Yes this query works in another query tool. It works in Cognos Impromptu, AS400 query tools.
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);
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
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.