tELTPostgresqlMap and context variables

Five Stars

tELTPostgresqlMap and context variables

Hi,

 

im using Talend Open Studio for Big Data V7.1.1

 

I got a tELTPostgresqlMap which is getting data from some tELTPostgresqlInputs.

I want to join these inputs with the help of context variables which get set before.

Also i want to use a context variable for where clauses.

 

The context variable always gets escaped and queries break.

 

Is there any way to actually use context variables as column values or for joins/where ?

 

 

Highlighted
Employee

Re: tELTPostgresqlMap and context variables

Hi,

 

   Did you print the output of context variables and verified whether they are having values loaded before running this step?

 

   I would also suggest to give the column values directly without unwanted escape characters and double quotes. 

 

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


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)
Five Stars

Re: tELTPostgresqlMap and context variables

Hi,

the values are set and correct.
I'm not Sure what you mean with

I would also suggest to give the column values directly without unwanted escape characters and double quotes.

I did not quote anything
Employee

Re: tELTPostgresqlMap and context variables

Hi,

 

    You have currently given the details as shown below.

image.png

 

 

Could you please try as shown below?

 

"SELECT column1,"+context.day+" FROM
 test t1
WHERE t1.day_column > "+context.day

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


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)
Five Stars

Re: tELTPostgresqlMap and context variables

Hi,

 

not sure how to do this as the context in a tPostgresqlmap always gets escaped by talend. :/

Also if in the final select the context value is in double quotes it is handled as column name and wont work. 

 

I got it working for string fields by using single quotes but its not working on string fields

 

invalid input syntax for integer: ""123432423456""

In the select the field looks like this:
'"5324324678"'
Employee

Re: tELTPostgresqlMap and context variables

Hi,

 

   Apologies for coming back late as I was busy with my real job and could not do data setup for this scenario.

 

   I have created a database in AWS MySQL database where I was able to extract the data with minor changes in the query. Please refer the below screen shots for details.

image.pngJob execution output

 

image.pngContext setup

 

image.pngstring to date conversion of context variable

 

 

Unfortunately, in my last post, I had overlooked the conversion of data from String to date. Once you convert the context variable to date format, you will be able to execute the query.

 

"SELECT id, name, day_column,STR_TO_DATE('"+context.day+ "', '%d/%m/%Y') as day 
FROM test
WHERE day_column >STR_TO_DATE('"+context.day+ "', '%d/%m/%Y')"

Please note that I am using MySQL for verification. So I have used MySQL functions for String to date conversion. Please refer the below link to get the corresponding PostgreSQL functions and you will have to use to_date(texttext) function for the same.

 

https://www.postgresql.org/docs/8.1/functions-formatting.html

 

 

Hope I have answered your query. Please remember to mark the topic as resolved before signing out for the benefit of our Talend community and Kudos are also welcome :-)

 

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


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)

Tutorial

Introduction to Talend Open Studio for Data Integration.

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.