topic Re: How to select data from multiple tables? in Talend Studio

How to select data from multiple tables?

Anonymous — Wed, 06 Jun 2012 16:09:43 GMT

Hi,
I have the following script :-
SET HEADING OFF
SET PAGESIZE 50000
SET LINESIZE 1200
SET COLSEP '|'
SET FEEDBACK OFF
COLUMN Channel_Name FORMAT A20 TRUNC
COLUMN Headline FORMAT A255 TRUNC
COLUMN Page_Title FORMAT A255 TRUNC
COLUMN Video_ID FORMAT 9999999999999
SPOOL dw_art_upd.csv
SELECT av.article_id AS Article_Id,
ch.display_name AS Channel_Name,
av.article_version_id AS Article_Version,
av.first_pub_date AS Published_Date,
av.created_date AS Created_Date,
av.headline AS Headline,
av.page_title AS Page_Title,
vc.external_video_id AS Video_ID
FROM channels ch,
video_config vc RIGHT OUTER JOIN articles_versions av
ON vc.video_config_id = TO_NUMBER(RTRIM(SUBSTR(av.article_text,DECODE(INSTR(av.article_text,'item name=video id='),0,NULL,INSTR(av.article_text,'item name=video id='))+19,6)))
WHERE av.published_flag='Y'
AND av.modified_date > TRUNC(sysdate)
AND av.article_id IN (SELECT article_id
FROM articles
WHERE created_date >= TO_DATE('01-SEP-11 00:00:01','DD-MON-YY HH24:MI S'))
AND ch.channel_id = av.channel_id

which needs to be scheduled as a job and the job should be running every day to fetch the data.
Since the query is bit complex am not sure if its possible to run this as a job from Talend Open Studio?
Can anyone please suggest me on how to go about this?

Many Thanks,
Manju

Re: How to select data from multiple tables?

Anonymous — Wed, 06 Jun 2012 21:09:32 GMT

Hi Manju,
You could paste the SELECT statement into t<db>Input, and send the result to a tFileOutputDelimited with | as the delimiter.
IIRC, you will have to manually enter the metadata for the t<db>Input fields.
Or, you could just try to execute the whole thing via a t<db>Row component. Not sure if that will handle the beginning part of the script.
Thanks,
Ben
Thanks,
Ben

Re: How to select data from multiple tables?

Anonymous — Thu, 07 Jun 2012 09:40:49 GMT

Many Thanks Ben, let me try this out and will let you know how it went.

Re: How to select data from multiple tables?

Anonymous — Thu, 07 Jun 2012 10:29:04 GMT

Hi,
I tried using tOracleInput , but not sure which table to choose as the query involves 3 tables connected by outer join.
Attached the screen shot . Please advise me on this.
Thanks,
Manju

Re: How to select data from multiple tables?

Anonymous — Thu, 07 Jun 2012 12:23:35 GMT

Please find the screen shot for the job with tORacleRow.

Re: How to select data from multiple tables?

_AnonymousUser — Mon, 19 Aug 2013 19:49:21 GMT

Can anyone please help assist with this question??
My team and I are facing the same problem and Talend doesn't seem to have an efficient way of handling this very simple task.

Re: How to select data from multiple tables?

Anonymous — Tue, 20 Aug 2013 01:35:48 GMT

What does the error say when you hover the mouse over the 'x' by the input?
As for specifying the schema when you have multiple joins, what worked for me was clicking guess schema and seeing if that worked. If not I manually edited the schema with the required columns.
Also make sure the whole query is in quotes.