Hi ,
I am firing a query in database and its working , but when the same query I am using in Datastage Connector Stage.
It is showing a error as :-
An exception has occured while trying to recieve the response from handler: An exception was recieved from handler: While parsing table name CDL_LND.CDL_INSTALMENT_BOOKOUTERCDL_LND.CDL_FIX_GNL_RECORDS , the connector detected an unexpected character at position 39.
I have checked the metadata of the columns , double checked for the special characters ... but does not find issue. The query is mentioned as :-
Query
-------
SELECT
CDL_INSTALMENT_BOOK.PK_ID,
CDL_INSTALMENT_BOOK.KEY,
CDL_INSTALMENT_BOOK.EXTRACT_DATE,
CDL_INSTALMENT_BOOK.BRAN,
CDL_INSTALMENT_BOOK.CLIENT,
CDL_INSTALMENT_BOOK.REF,
CDL_INSTALMENT_BOOK.NO,
CDL_INSTALMENT_BOOK.DEPOSIT,
CDL_INSTALMENT_BOOK.CHARGES,
CDL_INSTALMENT_BOOK.INST,
CDL_INSTALMENT_BOOK.REPNO,
CDL_INSTALMENT_BOOK.LOAN,
CDL_INSTALMENT_BOOK.OWED,
CDL_INSTALMENT_BOOK.SCHEME,
CDL_INSTALMENT_BOOK.REFERENCE,
CDL_INSTALMENT_BOOK.STATUS,
CDL_INSTALMENT_BOOK.PAY,
CDL_INSTALMENT_BOOK.LDATE,
CDL_INSTALMENT_BOOK.PLESS,
CDL_INSTALMENT_BOOK.BXDATE,
CDL_INSTALMENT_BOOK.BANKNAME,
CDL_INSTALMENT_BOOK.BANKACC,
CDL_INSTALMENT_BOOK.BANKCODE,
CDL_INSTALMENT_BOOK.CARDNO,
CDL_INSTALMENT_BOOK.CARDSTART,
CDL_INSTALMENT_BOOK.CARDEND,
CDL_INSTALMENT_BOOK.CARDISSUE,
CDL_FIX_GNL_RECORDS.PTYPE AS PTYPE,
CDL_HH_VAR_POLCY_RECORDS.SCHEME AS CCCH,
CDL_PRVT_CAR_VAR_POLCY_RECORDS.SCHEME AS CCCP
FROM
CDL_LND.CDL_INSTALMENT_BOOK
LEFT OUTER JOIN
CDL_LND.CDL_FIX_GNL_RECORDS CDL_FIX_GNL_RECORDS
ON
(CDL_INSTALMENT_BOOK.CLIENT = CDL_FIX_GNL_RECORDS.CLIENT) AND (CDL_INSTALMENT_BOOK.REF = CDL_FIX_GNL_RECORDS.REF)
LEFT OUTER JOIN
CDL_LND.CDL_PRVT_CAR_VAR_POLCY_RECORDS
ON
(CDL_INSTALMENT_BOOK.CLIENT = CDL_PRVT_CAR_VAR_POLCY_RECORDS.CLIENT) AND (CDL_INSTALMENT_BOOK.REF = CDL_PRVT_CAR_VAR_POLCY_RECORDS.REF)
LEFT OUTER JOIN
CDL_LND.CDL_HH_VAR_POLCY_RECORDS
ON
( CDL_INSTALMENT_BOOK.CLIENT = CDL_HH_VAR_POLCY_RECORDS.CLIENT) AND (CDL_INSTALMENT_BOOK.REF = CDL_HH_VAR_POLCY_RECORDS.REF)
Query in Oracle Connector Stage
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 64
- Joined: Fri Feb 05, 2010 5:29 am
- Location: India, UK
-
- Participant
- Posts: 64
- Joined: Fri Feb 05, 2010 5:29 am
- Location: India, UK
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 64
- Joined: Fri Feb 05, 2010 5:29 am
- Location: India, UK
The issue is resolved , there was a small bug in the Query... I have not mentioned the alias of CDL_INSTALMENT_BOOK table marked in Bold Letters. I was not able to diagnose because the query was working in database + Oracle Enterprise stage.
Thanks for all the inputs.
The query is given below:-
Query
-------
SELECT
CDL_INSTALMENT_BOOK.PK_ID,
CDL_INSTALMENT_BOOK.KEY,
CDL_INSTALMENT_BOOK.EXTRACT_DATE,
CDL_INSTALMENT_BOOK.BRAN,
CDL_INSTALMENT_BOOK.CLIENT,
CDL_INSTALMENT_BOOK.REF,
CDL_INSTALMENT_BOOK.NO,
CDL_INSTALMENT_BOOK.DEPOSIT,
CDL_INSTALMENT_BOOK.CHARGES,
CDL_INSTALMENT_BOOK.INST,
CDL_INSTALMENT_BOOK.REPNO,
CDL_INSTALMENT_BOOK.LOAN,
CDL_INSTALMENT_BOOK.OWED,
CDL_INSTALMENT_BOOK.SCHEME,
CDL_INSTALMENT_BOOK.REFERENCE,
CDL_INSTALMENT_BOOK.STATUS,
CDL_INSTALMENT_BOOK.PAY,
CDL_INSTALMENT_BOOK.LDATE,
CDL_INSTALMENT_BOOK.PLESS,
CDL_INSTALMENT_BOOK.BXDATE,
CDL_INSTALMENT_BOOK.BANKNAME,
CDL_INSTALMENT_BOOK.BANKACC,
CDL_INSTALMENT_BOOK.BANKCODE,
CDL_INSTALMENT_BOOK.CARDNO,
CDL_INSTALMENT_BOOK.CARDSTART,
CDL_INSTALMENT_BOOK.CARDEND,
CDL_INSTALMENT_BOOK.CARDISSUE,
CDL_FIX_GNL_RECORDS.PTYPE AS PTYPE,
CDL_HH_VAR_POLCY_RECORDS.SCHEME AS CCCH,
CDL_PRVT_CAR_VAR_POLCY_RECORDS.SCHEME AS CCCP
FROM
CDL_LND.CDL_INSTALMENT_BOOK CDL_INSTALMENT_BOOK
LEFT OUTER JOIN
CDL_LND.CDL_FIX_GNL_RECORDS CDL_FIX_GNL_RECORDS
ON
(CDL_INSTALMENT_BOOK.CLIENT = CDL_FIX_GNL_RECORDS.CLIENT) AND (CDL_INSTALMENT_BOOK.REF = CDL_FIX_GNL_RECORDS.REF)
LEFT OUTER JOIN
CDL_LND.CDL_PRVT_CAR_VAR_POLCY_RECORDS
ON
(CDL_INSTALMENT_BOOK.CLIENT = CDL_PRVT_CAR_VAR_POLCY_RECORDS.CLIENT) AND (CDL_INSTALMENT_BOOK.REF = CDL_PRVT_CAR_VAR_POLCY_RECORDS.REF)
LEFT OUTER JOIN
CDL_LND.CDL_HH_VAR_POLCY_RECORDS
ON
( CDL_INSTALMENT_BOOK.CLIENT = CDL_HH_VAR_POLCY_RECORDS.CLIENT) AND (CDL_INSTALMENT_BOOK.REF = CDL_HH_VAR_POLCY_RECORDS.REF)
Thanks for all the inputs.
The query is given below:-
Query
-------
SELECT
CDL_INSTALMENT_BOOK.PK_ID,
CDL_INSTALMENT_BOOK.KEY,
CDL_INSTALMENT_BOOK.EXTRACT_DATE,
CDL_INSTALMENT_BOOK.BRAN,
CDL_INSTALMENT_BOOK.CLIENT,
CDL_INSTALMENT_BOOK.REF,
CDL_INSTALMENT_BOOK.NO,
CDL_INSTALMENT_BOOK.DEPOSIT,
CDL_INSTALMENT_BOOK.CHARGES,
CDL_INSTALMENT_BOOK.INST,
CDL_INSTALMENT_BOOK.REPNO,
CDL_INSTALMENT_BOOK.LOAN,
CDL_INSTALMENT_BOOK.OWED,
CDL_INSTALMENT_BOOK.SCHEME,
CDL_INSTALMENT_BOOK.REFERENCE,
CDL_INSTALMENT_BOOK.STATUS,
CDL_INSTALMENT_BOOK.PAY,
CDL_INSTALMENT_BOOK.LDATE,
CDL_INSTALMENT_BOOK.PLESS,
CDL_INSTALMENT_BOOK.BXDATE,
CDL_INSTALMENT_BOOK.BANKNAME,
CDL_INSTALMENT_BOOK.BANKACC,
CDL_INSTALMENT_BOOK.BANKCODE,
CDL_INSTALMENT_BOOK.CARDNO,
CDL_INSTALMENT_BOOK.CARDSTART,
CDL_INSTALMENT_BOOK.CARDEND,
CDL_INSTALMENT_BOOK.CARDISSUE,
CDL_FIX_GNL_RECORDS.PTYPE AS PTYPE,
CDL_HH_VAR_POLCY_RECORDS.SCHEME AS CCCH,
CDL_PRVT_CAR_VAR_POLCY_RECORDS.SCHEME AS CCCP
FROM
CDL_LND.CDL_INSTALMENT_BOOK CDL_INSTALMENT_BOOK
LEFT OUTER JOIN
CDL_LND.CDL_FIX_GNL_RECORDS CDL_FIX_GNL_RECORDS
ON
(CDL_INSTALMENT_BOOK.CLIENT = CDL_FIX_GNL_RECORDS.CLIENT) AND (CDL_INSTALMENT_BOOK.REF = CDL_FIX_GNL_RECORDS.REF)
LEFT OUTER JOIN
CDL_LND.CDL_PRVT_CAR_VAR_POLCY_RECORDS
ON
(CDL_INSTALMENT_BOOK.CLIENT = CDL_PRVT_CAR_VAR_POLCY_RECORDS.CLIENT) AND (CDL_INSTALMENT_BOOK.REF = CDL_PRVT_CAR_VAR_POLCY_RECORDS.REF)
LEFT OUTER JOIN
CDL_LND.CDL_HH_VAR_POLCY_RECORDS
ON
( CDL_INSTALMENT_BOOK.CLIENT = CDL_HH_VAR_POLCY_RECORDS.CLIENT) AND (CDL_INSTALMENT_BOOK.REF = CDL_HH_VAR_POLCY_RECORDS.REF)
I was facing same issue with my query where I was using INNER JOIN.
SELECT
count(*)
FROM EPIMART.MUT_FUD_TRANS_FACT_0_V MFTF
INNER JOIN
EPIMART.SECURITIES_0_V SECURITIES
ON MFTF.SECURITIES_KEY = SECURITIES.SECURITIES_KEY
INNER JOIN
EPIMART.SECURITIES_0_V TO_SECURITIES
ON MFTF.TO_SECURITIES_KEY = TO_SECURITIES.SECURITIES_KEY
INNER JOIN EPIMART.TRANS_TYPE_METHOD_0_V TTM
ON MFTF.TRANS_TYPE_METHOD_KEY = TTM.TRANS_TYPE_METHOD_KEY
WHERE MFTF.TRANSTYPE_KEY = 20188
AND TRUNC(MONTHS_BETWEEN(TO_DATE(\'2010-12-13\',\'YYYY-MM-DD\'),MFTF.DATE_KEY)) <= 13;
It worked after I replaced INNER JOIN with JOIN.
SELECT
count(*)
FROM EPIMART.MUT_FUD_TRANS_FACT_0_V MFTF
INNER JOIN
EPIMART.SECURITIES_0_V SECURITIES
ON MFTF.SECURITIES_KEY = SECURITIES.SECURITIES_KEY
INNER JOIN
EPIMART.SECURITIES_0_V TO_SECURITIES
ON MFTF.TO_SECURITIES_KEY = TO_SECURITIES.SECURITIES_KEY
INNER JOIN EPIMART.TRANS_TYPE_METHOD_0_V TTM
ON MFTF.TRANS_TYPE_METHOD_KEY = TTM.TRANS_TYPE_METHOD_KEY
WHERE MFTF.TRANSTYPE_KEY = 20188
AND TRUNC(MONTHS_BETWEEN(TO_DATE(\'2010-12-13\',\'YYYY-MM-DD\'),MFTF.DATE_KEY)) <= 13;
It worked after I replaced INNER JOIN with JOIN.
If you don't fail now and again, it's a sign you're playing it safe.