Below query is returnig the result sucessfully when i execute in DBVisualizer and EVEN in DBMS SQL flow of Data Migrator. I.E. IT IS GIVING THE RESULT IN DB EDITOR, AND DBMS_SQL FLOW OF DM AS WELL
But after I save the flow, and whenever I reopen the flow, it is displaying the error,
"cannot parse DBMS sql SQL1014N
------------------------------- select * from stvoc.TSN_B2B_FFT_STAGING_MESSAGE T1 inner join ossf.vos_customer_account_rel T2 on T2.ACCT_SUBSYS_MNEMONIC_CD = 'DD' AND T2.RELATN_TYPE_CD = 'SOL' AND LPAD(trim(T1.CLIENT_ID),12,'0' ) = LPAD(trim(T2.ACCT_ID),12,'0' ) AND LPAD(trim(T2.PARTY_ID),15,'0' ) NOT IN (SELECT TD.CID FROM stvoc.TSN_B2B_SAMU_CID TD) inner join (select PREF_LANG_CD, LEGAL_NM, LPAD(trim(PARTY_ID),15,'0' )as vos_customer_party_id , PARTY_TYPE_CD from ossf.vos_customer) T3 on LPAD(trim(T2.PARTY_ID),15,'0') = T3.vos_customer_party_id AND T3.PARTY_TYPE_CD IN('COMBS','COMFR','COMAB','COMFN','PERSN') inner join (select CLIENT_ID as sf_client_id,CONTACT_FIRST_NAME,CONTACT_LAST_NAME,SALUTATION,CONTACT_TYPE,CONTACT_TITLE,EMAIL,CONTACT_DIRECT_PHONE_NUMBER,CONTACT_MOBILE_PHONE_NUMBER FROM stvoc.TSN_B2B_CUST_SURVEY_CC_SF_STG) T4 on T3.vos_customer_party_id = LPAD(trim(T4.sf_client_id),15,'0') inner join (select trim(FIRSTNAME) ||' '|| trim(LASTNAME) as AGENT_NAME,LPAD(trim(EMPLOYEENUMBER),10,'0') as hr_emp_id FROM stvoc.TSN_EMPLOYEES_AT_WORK_VIEW_FULL) T5 ON T5.hr_emp_id = LPAD(trim(T1.AGENT_ID),10,'0') WHERE T1.CREATION_EMP_TEAM_NM IN(SELECT TA.TFCFR_CMM_TEAM_NBR FROM stvoc.TSN_FFT_TFCFR_CMM_TEAM_NBR_REF_B2B TA) AND T1.TRANSACTION_CODE IN(SELECT TB.TFCFR_TXN_CODE FROM stvoc.TSN_FFT_TFCFR_TXN_CODE_REF_B2B TB) AND T1.CREATION_EMP_TRANSIT_NUM IN(SELECT TC.TRANSIT FROM stvoc.TSN_FFT_TRANSIT_REF_B2B TC)This message has been edited. Last edited by: FP Mod Chuck,
September 19, 2017, 08:27 AM
Clif
What release of DataMigrator are you using? Please be sure you are using the current production Release 7.7.07 (aka 8.2).
Also what is the underlying relational database? Please ensure that the Properties for the DBMS SQL object that the Adapter value is set to the correct database.
Please provide the complete error message. Is there an option to "proceed anyway?" If you click OK is the select statement displayed in the SQL object?