Anybody working in Peoplesoft Upgrade Project

[pmadhavi]

Hi
I have a problem with the date stored in Shared container.
I have already posted in this forum sometime back;
as i did not get the required outcome, i am posting again.

We are working on Peoplesoft 8.9upgrade project.
The lastupdateddate will be stored in the shared container.
If we open and see the date value in the shared container, it will be in the
format yyyy-mm-dd hh:mi:ss

It has to be compared with a DATE column in the source every time the data gets loaded so that the latest data will only be loaded.

Now the where clause given in the source DRS looks like this

DTTM_SEC > %DateTimeIn('#LastModifiedDateTime#')[b/]

But if I give like this it's throwing an error saying
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

So I tried formatting the Date column as to_char(DTTM_SEC, 'yyyy-mm-dd hh24:miss') and also removed DateTimeIn for the macro.

Then the comparision is giving the incorrect number of records as the date formats are not in Sync.
Every time I insert a new record to the source table, the shared container is updated with the latest date; but the only probelm is instead of inserting one record, its inserting thousands of records.

We can give the date comparision in Transformer instead of giving it in Source DRS; but all the millions of records in the source will go to the transformer and there they will be filtered. So the performance will be low.
Because if we have only hundreds of records to be updated, and for that we move millions of records to the transformer.[b/]

Pls help me out in this.

[loveojha2]

Now the where clause given in the source DRS looks like this

DTTM_SEC > %DateTimeIn('#LastModifiedDateTime#')[b/]

But if I give like this it's throwing an error saying
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Can you please paste what is the value that is coming from #LastModifiedDateTime#.

So I tried formatting the Date column as to_char(DTTM_SEC, 'yyyy-mm-dd hh24:miss') and also removed DateTimeIn for the macro.

I guess it has nothing to do with DTTM_SEC, since it is a column (date column) from your table (oracle).

You can try this with the where clause as

Code: Select all

DTTM_SEC > To_DATE(%DateTimeIn('#LastModifiedDateTime#'),'YYYY-MM-DD HH24:MI:SS')

[shyamsrp]

Hi
I have a problem with the date stored in Shared container.
I have already posted in this forum sometime back;
as i did not get the required outcome, i am posting again.

We are working on Peoplesoft 8.9upgrade project.
The lastupdateddate will be stored in the shared container.
If we open and see the date value in the shared container, it will be in the
format yyyy-mm-dd hh:mi:ss

It has to be compared with a DATE column in the source every time the data gets loaded so that the latest data will only be loaded.

Now the where clause given in the source DRS looks like this

DTTM_SEC > %DateTimeIn('#LastModifiedDateTime#')[b/]

But if I give like this it's throwing an error saying
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

So I tried formatting the Date column as to_char(DTTM_SEC, 'yyyy-mm-dd hh24:miss') and also removed DateTimeIn for the macro.

Then the comparision is giving the incorrect number of records as the date formats are not in Sync.
Every time I insert a new record to the source table, the shared container is updated with the latest date; but the only probelm is instead of inserting one record, its inserting thousands of records.

We can give the date comparision in Transformer instead of giving it in Source DRS; but all the millions of records in the source will go to the transformer and there they will be filtered. So the performance will be low.
Because if we have only hundreds of records to be updated, and for that we move millions of records to the transformer.[b/]

Pls help me out in this.

Hi,

a) Is it PS delivered Map or custome one?
b) What is output of your routine activity in theSE? I guess, It should be like 2005-12-28 23:49:53.(assuming your DATE_SEC is Oracle Date and will be in the same format when it is brought over to DS.

[pmadhavi]

Pls find the query below:

1 select count(*) from <table> where to_char(dttm_sec)>to_char('2005-03-30 14:26:51');

and another query is as follows:

2 select count(*) from PS_LEDGER where dttm_stamp_sec> '30-Mar-05';

the first query returns 1309100 rows and the second query returns 1032525 rows.

The only difference in these queries is the date format in which they are compared.

There is a drastic change in the number of rows returned.

The same thing is happening when I give the date compare in Source DRS.

I tried not formatting the Date column DTTM_SEC which is coming from Oracle as suggested by loveojha2.
And also I formatted the LastModifiedDate to To_DATE(%DateTimeIn('#LastModifiedDateTime#'),'YYYY-MM-DD HH24:MI:SS')

Still its throwing the same error
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Suggestions are welcome

[loveojha2]

Pls find the query below:

1

Code: Select all

select count(*) from table where to_char(dttm_sec)>to_char('2005-03-30 14:26:51');

[/size]

Should be

Code: Select all

select count(*) from table where to_char(dttm_sec,'yyyy-mm-dd hh24:mi:ss')>to_char('2005-03-30 14:26:51','yyyy-mm-dd hh24:mi:ss');

2

Code: Select all

select count(*) from PS_LEDGER where dttm_stamp_sec> '30-Mar-05';

[/size]

the first query returns 1309100 rows and the second query returns 1032525 rows.

Should be

Code: Select all

select count(*) from PS_LEDGER where dttm_stamp_sec> to_date('30-Mar-05','dd-mon-yyyy');

If dates are to be compared best is to do it with to_date.

Moreover how many rows are getting selected when you do

Code: Select all

select count(*) from table where to_date(dttm_sec,'yyyy-mm-dd hh24:mi:ss')>to_date('2005-03-30 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date(dttm_sec,'yyyy-mm-dd hh24:mi:ss')<=to_date('2005-03-30 14:26:51','yyyy-mm-dd hh24:mi:ss');

And what is the value that you are giving in the parameter #LastModifiedDateTime#