Query in Oracle Connector Stage

[Ankur_KapoorUK]

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)

[chulett]

Guessing it is the fact that "CDL_INSTALMENT_BOOKOUTERCDL_LND" is 31 characters... could have sworn the limit was 30. What version of Oracle is this - server and client?

[Ankur_KapoorUK]

This is the Oracle client .....

I have put the same query in Oracle Enterprise stage .... and it is working fine....Not understanding what was the problem with connector Stage?

[chulett]

Doesn't answer my question at all, but fine. Connectors are new and still... buggy. Ping your official support provider and see if there is a patch.

[abhijain]

Chulett is right. Please check with your official support partners.

Connectors are buggy. We are also facing so many issues with the OCI Connector Stage.

[ray.wurlod]

Check too whether your other client is set to automatically truncate to legal names.

[Ankur_KapoorUK]

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)

[Ananda]

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.