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

[Anonymous]

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?

[TRF]

Can you clarify with 1 or 2 examples?

[Anonymous]

Hi,

In these case, those rows are empty and I can add any value inside.

E.G. numbers or letters, only one character 

Regards

row.JPG

[TRF]

Sorry, your question is absolutly not clear

[Anonymous]

Hi,

I have a column with empty rows and I need to add any character in those rows

No.Cntr.  --> No.Cntr.

(empty)         X

(empty)         X

(empty)         X

It's all I need.

Regards.

[Anonymous]

But others rows have determined values

No.Cntr.             -->    No.Cntr.

(empty)                       X

ADBF1209872           ADBF1209872

QWER0987654         QWER0987654

(empty)                      X

I hope you understand.

Regards.

[Anonymous]

Hi,

   Could you please try below code snippet?

Relational.ISNULL(row1.cntr)?"X":row1.cntr 

If the input value is NULL, it will replace it with value"X" and if it is having some data, it will be using the same data.

Now, if you want to parameterize the same instead of hard coded string value, please use a context variable at the position of "X". 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

[kavita03]

@Emmanuel Trejo​ can you please let me know if you got any solution for this problem, as i am facing the same issue, my query runs on redshift and failes at ETL job. It will be great if you share your solution.

Thanks in advance