odbc stage update table issue

[hsahay]

Hi,

I have issue with odbc stage upating the table.

i have below 2 columns coiming into odbc stage

COLUMN NAME SQL LENGTH NULLABLE
RUNDATE Date 10 yes
CLM_FILE_ID_IN Varchar 255 yes


there is only one row coming into odbc stage and value for these columns are

RUNDATE = Date
CLM_FILE_ID_IN = ('20140407-00','20140407-00-ITS')



and i am trying update table XTABLE using below query
and job failed with below eeors

Query:
UPDATE XTABLE SET ORIGINAL_LOAD_DT = ORCHESTRATE.RUNDATE WHERE (CLM_FILE_ID in ORCHESTRATE.CLM_FILE_ID_IN and ORIGINAL_LOAD_DT=(select max(ORIGINAL_LOAD_DT) from XTABLE ))

Ds error:

ODBC_Enterprise_36,0: Failure during execution of operator

logic.ODBC_Enterprise_36,0: Fatal Error:

node_node1: Player 11 terminated unexpectedly.

main_program: APT_PMsectionLeader(1, node1), player 11 - Unexpected exit status 1.



JOb ran fine when i hard code the query in odbc stage

UPDATE XTABLE SET ORIGINAL_LOAD_DT = '9999-01-01'
WHERE (CLM_FILE_ID in ('20140407-00','20140407-00-ITS') and ORIGINAL_LOAD_DT=(select max(ORIGINAL_LOAD_DT) from XTABLE ))


please advise how to fix this.

thanks in advance.

[rohitagarwal15]

What is the source for this date data ? is it coming from some database or you reading it from some file ??
what is your target database for loading the data ??

[hsahay]

Source for date data is oracle table ...traget database is netezza table.

issue is with the ORCHESTRATE.FILE_ID_IN

when i hard code the value for FILE_ID_IN in the query then job ran fine

UPDATE XTABLE SET ORIGINAL_LOAD_DT = ORCHESTRATE.RUNDATE WHERE FILE_ID in ('asdf','zxcv')

please advise how to reslove this.

[ray.wurlod]

You can not supply an IN list via an element of the ORCHESTRATE collection.