Extraction criteria based on datetime in Extractor

[Former Member]

Dear Experts,

In my source ECC is very huge volume of data more than 4 crore records.

I need to use Extractor and need to extract every two months data on everyday (daily basis) starting from 2008.

The data will be loading to oracle data warehousing.

Kindly provide the solution

Regards

Selvam

[former_member186897 · Accepted solution]

You can do this way:

Declare two global variables $start_date and $end_Date and set those as following in a script.

$start_date =add_months(sysdate( ),-2);

$end_date =sysdate( );

Your where clause of Query Transform would look  like as under while extracting.

Table.Date_Field >=$start_date and Table.Date_Field <=$end_Date

Hope this helps.

[Former Member · Accepted solution]

Hi,

Can you give detail information about Table.

Regards,

Manoj.

[former_member189153 · Accepted solution]

Hi Manoj,

Table nothing but your source, track and drop your source date field to where clause and put the above mentioned condition

[Former Member · Accepted solution]

Hello Ansari,

Q:"extract every two months data on everyday (daily basis) starting from 2008."

I think the formula you have mentioned is not sufficient for the above req..

Since your formula is build with SYSDATE( )..

correct me if i'm wrong...

Regards

Subbarao M

[former_member186897 · Accepted solution]

Yes, the logic will give 2 months data at a time.

But yes, if it is one time extraction then start date and end date to be made dynamic where start date and end date will be incremented by 2 months after every run starting from 2008. Extraction will happen inside a loop and it shall continue till reaches the current date. 

[Former Member · Accepted solution]

Hi,

I have used global variable

$GV_ENDDATE = sysdate()-1

$GV_STARTDATE = (add_months( $GV_ENDDATE, -2));

This $GV_STARTDATE  will be compared with source ECC table ERDAT field , so it will extract 2 months data every day.

Thanks

Selva