Return query from postgreSQL into Talend Flow

[Anonymous]

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

[TRF]

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

Replace 

"Traffic"

by

\"Traffic\"

 and so on

[Anonymous]

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);
"

[TRF · Accepted solution]

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

Got it?