One Star

[resolved] Read data by months, Iterate?

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
1 ACCEPTED SOLUTION

Accepted Solutions
One Star

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

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
11 REPLIES
One Star

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

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
One Star

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

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
One Star

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

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

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

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
One Star

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

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
One Star

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

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
One Star

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

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

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

Hi
Have you changed tFixedFlowInput into tFileInputDelimited?
Regards,
Pedro
One Star

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

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
One Star

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

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
One Star

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

Hi
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