Vertica with Informatica 9.5 compatibility

edited May 2017 in General Discussion

Hi,I am using Informatica 9.5 with the HP vertica plug in.I am having a informatica mapping that uses Vertica table as the source.I have a source qualifier query in my informatica session.I am using a parameter in my source qualifier query.When I try running the workflow, it fails with the following error.
" [ERROR] SQL error: [Vertica]VJDBC Missing a matching closing delimiter in statement ".

Please find my Source qualifier query below,
with OD_AC as
(select distinct b.fds_insm_id,g.GL_ACCT_NUM, g.MGT_UNIT_CD
from FBI.t_arn_fds_bal b, FBI.T_ARN_GL_ACCT_CHT_FLD_SET G, FBI.T_Fbi_dim_glac a, FBI.t_Arn_fds_insm i
where b.src_sys_cd = 'CAS' and b.fds_insm_id = i.fds_insm_id
and i.FDS_INSM_TYPE_CD_ID=1 and b.FDS_GL_ACCT_CHT_FLD_ID=g.FDS_GL_ACCT_CHT_FLD_ID
and g.GL_ACCT_NUM=a.GLAC_NO
and a.PRM_GLAC_LVL_10_CD in ( 'E11009','BNYODR','MELOVD','E10052','BNYOVD','1406600'))

select max(distinct g.MGT_UNIT_CD) bnym_v_cas_od_alt_mu ,
b.fds_insm_id FDS_INSM_ID
from OD_AC, FBI.t_arn_fds_bal b, FBI.T_ARN_GL_ACCT_CHT_FLD_SET G, FBI.T_Fbi_dim_glac a, FBI.t_Arn_fds_insm i
where b.src_sys_cd='CAS' and OD_AC.fds_insm_id=b.fds_insm_id
and b.fds_insm_id=i.fds_insm_id and i.FDS_INSM_TYPE_CD_ID=1
and b.FDS_GL_ACCT_CHT_FLD_ID=g.FDS_GL_ACCT_CHT_FLD_ID
and g.GL_ACCT_NUM=a.GLAC_NO
and a.PRM_GLAC_LVL_10_CD not in ('E11009','BNYODR','MELOVD','E10052','BNYOVD','1406600')
and a.PRM_GLAC_LVL_4_CD<>'EQWMIN' and OD_AC.MGT_UNIT_CD<>g.MGT_UNIT_CD
and TO_CHAR(b.fds_acctg_perd_id)=$$Accounting_Period
group by b.fds_insm_id;

I tried enclosing the parameter with double quotes and single quotes i.e. "$$Accounting_Period" & ' Accounting_Period' , but still issue persists.

Please send some ideas to resolve the same.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file