Five 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.

  • Data Integration
1 ACCEPTED SOLUTION

Accepted Solutions
Nine Stars TRF
Nine 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
3 REPLIES
Nine Stars TRF
Nine 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
Five 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);
"
Nine Stars TRF
Nine 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