topic Re: [resolved] Read data by months, Iterate? in Talend Studio

[resolved] Read data by months, Iterate?

Anonymous — Wed, 14 Mar 2012 10:44:52 GMT

Hi,
I have the following scenario:
tOracleInput ---------> tMap ---------> tOracleOutput
|
tOracleInput --------------|
The issue is that the connection where I take the data from with tOracleInput is really bad, and I have cuts in that connection.
Some of the tables have many rows that I have to take, so I wan't to take the data from the big tables month by month using one of the date fields in the table, but I don't know how to achieve it, maybe a context variable and iterating?
Can someone guide me on how to do it?
Thanks in advance,
Regards,
Khor

Re: [resolved] Read data by months, Iterate?

Anonymous — Thu, 15 Mar 2012 02:33:09 GMT

Hi Khor
I don't know what this job does in tMap.
But according to your requirement, you can create part of your job as follow.
tFixedFlowInput--main-->tJavaRow
|
Iterate
|
tOracleInput--->other components...
tFixedFlowInput: Check "Use Inline Table" and type all months which you want to extract.
tJavaRow: type nothing in it.
tOracleInput: edit the Query like this.

"select ColumnName1, ColumnName2...from TableName where Date_Field ="+row1.Month

Regards,
Pedro

Re: [resolved] Read data by months, Iterate?

Anonymous — Thu, 15 Mar 2012 08:30:23 GMT

Hi Pedro,
Thanks for your reply, I tried to reproduce what you say but I failed. When I try to run the job I get an error saying that it can't turn a int to a BigDecimal or to a Date. I know that one of the things that i have wrong is how I put the month in tFixedFlowInput but I can't guess how to do it.
The final objective of this is not to show the data of one month with tLogRow, I used it just to know it it would work with the big job.
See screenshot below for more information.
Regards,
Khor

Re: [resolved] Read data by months, Iterate?

Anonymous — Thu, 15 Mar 2012 08:50:34 GMT

Hi Khor
You can use tFileInputDelimited to load a file instead of tFixedFlowInput.
Then you will get rid of this error.
Regards,
Pedro

Re: [resolved] Read data by months, Iterate?

Anonymous — Thu, 15 Mar 2012 09:04:49 GMT

Hi Pedro,
I can now execute my job using "TalendDate.parseDate("dd-MM-yyyy","01-02-2003")" in tFixedFlowInput, but now I get a Oracle error but I see the query ok.
Error:

Exception in component tOracleInput_1
java.sql.SQLException: ORA-00933

Query:

"SELECT 
  HL_STG_STK.PRUEBA_CAMIONES_OUT.ID_OPERACION, 
  HL_STG_STK.PRUEBA_CAMIONES_OUT.CAMION, 
  HL_STG_STK.PRUEBA_CAMIONES_OUT.EP, 
  HL_STG_STK.PRUEBA_CAMIONES_OUT.ET, 
  HL_STG_STK.PRUEBA_CAMIONES_OUT.ST, 
  HL_STG_STK.PRUEBA_CAMIONES_OUT.SP
FROM HL_STG_STK.PRUEBA_CAMIONES_OUT
WHERE HL_STG_STK.PRUEBA_CAMIONES_OUT.ET="+row1.Month+""

Regards,
Khor

Re: [resolved] Read data by months, Iterate?

Anonymous — Thu, 15 Mar 2012 09:38:47 GMT

Hi Pedro,
I get another error with this one too. Like the quotes are not well placed. Don't you open quotes two times and close three in your code?
Regards,
Khor

Re: [resolved] Read data by months, Iterate?

Anonymous — Thu, 15 Mar 2012 09:48:32 GMT

Hi Khor
I have corrected my last post and remove double quotation marks.
One more thing. Use tFileInputDelimted instead of tFixedFlowInput.
There is something wrong with Iterate of tFixedFlowInput.
The error ORA-00933 is due to tFixedFlowInput and the data type of this column.
You won't get any error this time.
Regards,
Pedro

Re: [resolved] Read data by months, Iterate?

Anonymous — Thu, 15 Mar 2012 09:59:31 GMT

Hi Pedro
I'm getting the same error (ORA-00933) without quotes.
Regards,
Khor

Re: [resolved] Read data by months, Iterate?

Anonymous — Thu, 15 Mar 2012 10:00:30 GMT

Hi
Have you changed tFixedFlowInput into tFileInputDelimited?
Regards,
Pedro

Re: [resolved] Read data by months, Iterate?

Anonymous — Thu, 15 Mar 2012 10:04:01 GMT

Hi Pedro
Yes, I changed it and I have only one month in it.
My csv file looks like this:

01-02-2003;

Maybe I have to write the month in another format?
The schema field is Data type.
Regards,
Khor

Re: [resolved] Read data by months, Iterate?

Anonymous — Thu, 15 Mar 2012 10:14:51 GMT

Hi
I solved the errors by changing the schema of tFileInputDelimited from date to string and writing the query like this:

"SELECT 
  HL_STG_STK.PRUEBA_CAMIONES_OUT.ID_OPERACION, 
  HL_STG_STK.PRUEBA_CAMIONES_OUT.CAMION, 
  HL_STG_STK.PRUEBA_CAMIONES_OUT.EP, 
  HL_STG_STK.PRUEBA_CAMIONES_OUT.ET, 
  HL_STG_STK.PRUEBA_CAMIONES_OUT.ST, 
  HL_STG_STK.PRUEBA_CAMIONES_OUT.SP
FROM HL_STG_STK.PRUEBA_CAMIONES_OUT
WHERE HL_STG_STK.PRUEBA_CAMIONES_OUT.ET=to_date('"+row1.Month+"', 'yyyy/mm/dd hh24:mi:ss')"

Now the problem is that I read 0 rows but I guess that's a problem with my data or my database so thank you for all your help Pedro.
Regards,
Khor

Re: [resolved] Read data by months, Iterate?

Anonymous — Thu, 15 Mar 2012 10:21:38 GMT

SELECT 
  HL_STG_STK.PRUEBA_CAMIONES_OUT.ID_OPERACION, 
  HL_STG_STK.PRUEBA_CAMIONES_OUT.CAMION, 
  HL_STG_STK.PRUEBA_CAMIONES_OUT.EP, 
  HL_STG_STK.PRUEBA_CAMIONES_OUT.ET, 
  HL_STG_STK.PRUEBA_CAMIONES_OUT.ST, 
  HL_STG_STK.PRUEBA_CAMIONES_OUT.SP
FROM HL_STG_STK.PRUEBA_CAMIONES_OUT
WHERE HL_STG_STK.PRUEBA_CAMIONES_OUT.ET=to_date('"+row1.Month+"', 'dd-MM-yyyy')"

Regards,
Pedro