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

Highlighted
Six Stars

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

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?

 

Sixteen Stars TRF
Sixteen Stars

Re: add value

Can you clarify with 1 or 2 examples?

TRF
Six Stars

Re: add value

Hi,

 

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

 

E.G. numbers or letters, only one character 

 

Regards

 

Sixteen Stars TRF
Sixteen Stars

Re: add value

Sorry, your question is absolutly not clear

TRF
Six Stars

Re: add value

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.

 

Six Stars

Re: add value

But others rows have determined values

 

No.Cntr.             -->    No.Cntr.

(empty)                       X

ADBF1209872           ADBF1209872

QWER0987654         QWER0987654

(empty)                      X

 

I hope you understand.

 

Regards.

 

Employee

Re: add value

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 :-)

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog