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 ?
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.
You have currently given the details as shown below.
Could you please try as shown below?
"SELECT column1,"+context.day+" FROM test t1 WHERE t1.day_column > "+context.day
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:
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.
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(text, text) function for the same.
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 :-)
Talend named a Leader.
Kickstart your first data integration and ETL projects.
Learn how to make your data more available, reduce costs and cut your build time
Read about OTTO's experiences with Big Data and Personalized Experiences
Take a look at this video about Talend Integration with Databricks