Return query from postgreSQL into Talend Flow

Six Stars

Return query from postgreSQL into Talend Flow

"
SELECT 2 AS rowtype
     , source AS "TrafficTypes_Name"
     , COUNT(source) AS "Traffic"
     , to_char(week,'MM/dd/yyyy') AS "Week_Ending"
  FROM amazon.infocentertracker
  where project_id = 'LFC'
GROUP 
    BY source
     , to_char(week,'MM/dd/yyyy')
UNION ALL
SELECT 1 
     , 'Visitor Center Walk-ins'
     , COUNT(source)
     , to_char(week,'MM/dd/yyyy') as week 
  FROM amazon.infocentertracker
  where project_id = 'LFC'
GROUP 
    BY to_char(week,'MM/dd/yyyy')
ORDER 
    BY "Week_Ending"
     , rowtype
"

I am using tpostgresqlInput to return results of the above query into Talend but it throws an error saying "Syntax Error On token, delete these tokens."

 

Is tpostgreSQLinput the right component to use for such instances.

 

Interestingly enough, when I do "select * from amazon.infocentertracker", and hit Guess Schema tpostgreSQLinput returns the schema.

 

Any insights would be appreciated.


Accepted Solutions
Fifteen Stars TRF
Fifteen Stars

Re: Return query from postgreSQL into Talend Flow

You don't need the () but you have to change to remove " after infocentertracker in your query.

Got it?

 


TRF

All Replies
Fifteen Stars TRF
Fifteen Stars

Re: Return query from postgreSQL into Talend Flow

Your SQL query contains " inside which is not allowed here except if you protect them by an "\".

Replace 

"Traffic"

by

\"Traffic\"

 and so on

 


TRF
Six Stars

Re: Return query from postgreSQL into Talend Flow

Thanks TRF for the rapid response, now it states Syntax Error, insert ")" to complete MethodInvocation. It does not work even after I inserted the ().

 

"
(SELECT 2 AS rowtype
     , source AS \"TrafficTypes_Name\"
     , COUNT(source) AS \"Traffic\"
     , to_char(week,'MM/dd/yyyy') AS \"Week_Ending\"
  FROM amazon.infocentertracker"
  where project_id = 'LFC'
GROUP 
    BY source
     , to_char(week,'MM/dd/yyyy')
UNION ALL
SELECT 1 
     , 'Visitor Center Walk-ins'
     , COUNT(source)
     , to_char(week,'MM/dd/yyyy') as week 
  FROM amazon.infocentertracker
  where project_id = 'LFC'
GROUP 
    BY to_char(week,'MM/dd/yyyy')
ORDER 
    BY \"Week_Ending\"
     , rowtype);
"
Fifteen Stars TRF
Fifteen Stars

Re: Return query from postgreSQL into Talend Flow

You don't need the () but you have to change to remove " after infocentertracker in your query.

Got it?

 


TRF

Calling Talend Open Studio Users

The first 100 community members completing the Open Studio survey win a $10 gift voucher.

Start the survey

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 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Downloads and Trials

Test drive Talend's enterprise products.

Downloads