topic Re: Return query from postgreSQL into Talend Flow in Talend Studio

Return query from postgreSQL into Talend Flow

Anonymous — Fri, 28 Apr 2017 15:14:43 GMT

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

Re: Return query from postgreSQL into Talend Flow

TRF — Fri, 28 Apr 2017 15:18:47 GMT

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

Replace

"Traffic"

\"Traffic\"

and so on

Re: Return query from postgreSQL into Talend Flow

Anonymous — Fri, 28 Apr 2017 15:32:47 GMT

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

Re: Return query from postgreSQL into Talend Flow

TRF — Fri, 28 Apr 2017 15:49:13 GMT

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

Got it?