Incremental Load based on Time

[marishnagendran]

Hi All,

I want to increment the data based on time bases

We are using Postgresql Database.

My Script

temp:
NoConcatenate
LOAD max(last_update_date) as LastRecordDate

From qvd;

LET vFromDate = Timestamp#(date(date((peek('LastRecordDate', 0, 'temp')),'MM/DD/YYYY hh:mm:ss tt'),'MM/DD/YYYY hh:mm:ss tt'));

,customermasterhdr c
,companymaster q
,ITEMGROUP_WISE_GC_BUSINESS_HEAD_HDR PM
WHERE f.customer_id=c.cust_account_id
and f.org_id=q.org_id
AND F.s_item_group=PM.ITEMGROUP(+)
and f.last_updated_date::date > '$(vFromDate)'
)H
)D
)P
)Y
) x
order by customer_trx_id,invoice_date;

It takes only Date  as increment it not consider the time..kindly help me.

[pradosh_thakur]

temp:
NoConcatenate
LOAD max(last_update_date) as LastRecordDate

From qvd;

LET vFromDate = Timestamp(date(peek('LastRecordDate', 0, 'temp')),'MM/DD/YYYY hh:mm:ss tt');

 

,customermasterhdr c
,companymaster q
,ITEMGROUP_WISE_GC_BUSINESS_HEAD_HDR PM
WHERE f.customer_id=c.cust_account_id
and f.org_id=q.org_id
AND F.s_item_group=PM.ITEMGROUP(+)
and f.last_updated_date::date > '$(vFromDate)'
)H
)D
)P
)Y
) x
order by customer_trx_id,invoice_date;

Try the above. The only change i did is in the variable. You have used hh:mm:tt with the date, only thing i did was convert it into date an then to timestamp. You may need to tweak this a bit but the logic will remain same

-Pradosh

[marishnagendran]

Hi Thakur

This is also not working.I'm Tried in many ways still not yet working in Postgresql 

[pradosh_thakur]

As i said you are seeing the data and you can tweak it as required, only thing is to check if the date/ time format is fine .

You can call me Pradosh. 🙂 

-Pradosh

[marishnagendran · Accepted solution]

Got it Pradosh,

The thing i have missed in Query

,customermasterhdr c
,companymaster q
,ITEMGROUP_WISE_GC_BUSINESS_HEAD_HDR PM
WHERE f.customer_id=c.cust_account_id
and f.org_id=q.org_id
AND F.s_item_group=PM.ITEMGROUP(+)
and f.last_updated_date::date > '$(vFromDate)'

In Query the  f.last_updated_date::date column is an error.The correct .last_updated_date

[pradosh_thakur]

That's awesome. Glad that you figured it out .

-Pradosh