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

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

Anonymous — Mon, 03 Jun 2019 20:11:16 GMT

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?

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

TRF — Mon, 03 Jun 2019 20:20:24 GMT

Can you clarify with 1 or 2 examples?

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

Anonymous — Mon, 03 Jun 2019 20:34:40 GMT

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

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

TRF — Mon, 03 Jun 2019 22:42:17 GMT

Sorry, your question is absolutly not clear

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

Anonymous — Mon, 03 Jun 2019 22:53:06 GMT

Hi,

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

No.Cntr. --> No.Cntr.

(empty) X

It's all I need.

Regards.

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

Anonymous — Mon, 03 Jun 2019 22:56:12 GMT

But others rows have determined values

No.Cntr. --> No.Cntr.

(empty) X

ADBF1209872 ADBF1209872

QWER0987654 QWER0987654

(empty) X

I hope you understand.

Regards.

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

Anonymous — Tue, 04 Jun 2019 03:47:53 GMT

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 🙂

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

kavita03 — Sat, 19 Sep 2020 18:05:09 GMT

@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