tRedshiftRow_5 - [Amazon](500310) Invalid operation: This type of correlated subquery pattern is not supported due to internal error;

[desanip]

I have a sql query that i am having issues executing inside my etl job:

"delete" + context.Redshift_data_lake_venus + "." + context.cntx_destination_table_name + " where lngGameHistory in (select b.lngGameHistory from " + context.Redshift_data_lake_venus + ".idbs_tblgamehistory b where " + context.cntx_date_filter_name + " >= '" + context.cntx_date_id_begin + "' and " + context.cntx_date_filter_name + " <= '" + context.cntx_date_id_end + "' )"

I can execute the hardcoded subquery in redshift but its not working in my etl job

The error is as follows: tRedshiftRow_5 - Amazon Invalid operation: This type of correlated subquery pattern is not supported due to internal error; Amazon Invalid operation: This type of correlated subquery pattern is not supported due to internal error;[FATAL]: tgtimdagamespos.job_incremental_idbs_tblgamehistorybay_6_0.job_Incremental_IDBS_TblGameHistoryBay - tMSSqlInput_1 Incorrect syntax near 'a'.

Can someone advice me on what i am doing wrong? How the code should be modified?

[vapukov]

Hi,

it is very hard to visual debug dynamic code without real structure and code

but what I do all the time when meets with similar issues - instead of tRedshiftRow put tJavaRow and print result code with

System.out.println (all your code there);

then try to run result code over the database, this is help in most of the cases

cheers, Vlad