Security Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

GTC Page Error and Nexaweb Error

ZiaNov 18 2009 — edited Nov 28 2009

hi all !
i am experiencing two problems. First one is that when i open GTC page on the Administrator and USer Console, i get this error message printed on top of the page

+"Provider Registration Framework Initialization failure occurred: The underlying XML parser that is used does not support the required JAXP version. Check the log files for more information."+

can anyone guide me what do i need to change in my web server or else?

other is that whenever i try to create noew workflow, the pop up window opens with following error:

+"Nexaweb Error Message+
+You don't have JAVA enabled or installed, click here for more info"+

i have checked the browser settings for java....its enabled...where is the problem???

Karthick2003

Try this.

update adam_test1 t1
   set balance = (select sum(balance) over(partition by account order by entry_date) val
                        from adam_test t2
                        where t1.rowid = t2.rowid)

Centinul

Thanks for posting sample data!

If you just want to calculate the running balance you could do the following:

SELECT  ACCOUNT
,       ENTRY_DATE
,       AMOUNT
,       SUM(AMOUNT) OVER (PARTITION BY ACCOUNT ORDER BY ENTRY_DATE) AS BALANCE
FROM    ADAM_TEST1
ORDER BY ACCOUNT
,       ENTRY_DATE

Which results in:

ACCO ENTRY_DATE      AMOUNT    BALANCE
---- ----------- ---------- ----------
1000 01-JAN-2000         50         50
1000 02-JAN-2000         40         90
1000 03-JAN-2000        -50         40
1000 04-JAN-2000         10         50
1000 05-JAN-2000       -250       -200
1000 06-JAN-2000        100       -100
1000 07-JAN-2000         50        -50
2000 01-JAN-2000         30         30
2000 02-JAN-2000         10         40
2000 03-JAN-2000       -520       -480
2000 04-JAN-2000        140       -340
2000 05-JAN-2000         -4       -344
2000 06-JAN-2000        120       -224
2000 07-JAN-2000         57       -167

You may be better off in this case making this calculation at real time instead of trying to store it.

1 person found this helpful

Frank Kulash

Hi,

Thanks for including the sample data in such a useful form!
Don't forget to post the results you want from that data.

You can get a running total with the analytic SUM function.
For example:

SELECT    a.*
,	  SUM (amount)	OVER ( PARTITION BY  account
	      		       ORDER BY	     entry_date
			     ) 	     AS sum_to_date
FROM	  adam_test1	a
ORDER BY  account
,     	  entry_date
;

Output:

ACCO ENTRY_DAT     AMOUNT    BALANCE SUM_TO_DATE
---- --------- ---------- ---------- -----------
1000 01-JAN-00         50                     50
1000 02-JAN-00         40                     90
1000 03-JAN-00        -50                     40
1000 04-JAN-00         10                     50
1000 05-JAN-00       -250                   -200
1000 06-JAN-00        100                   -100
1000 07-JAN-00         50                    -50
2000 01-JAN-00         30                     30
2000 02-JAN-00         10                     40
2000 03-JAN-00       -520                   -480
2000 04-JAN-00        140                   -340
2000 05-JAN-00         -4                   -344
2000 06-JAN-00        120                   -224
2000 07-JAN-00         57                   -167

If you want to actually store this in the table (which is probably not a good idea), use a MERGE statement, with a query like the one above in the USING clause.

Solomon Yakobson

Answer

Use MERGE:

merge
  into  adam_test1 t1
  using (
         select  rowid,
                 sum(amount) over(partition by account order by entry_date) balance
           from  adam_test1
        ) t2
  on (t1.rowid = t2.rowid)
  when matched
    then
      update set t1.balance = t2.balance
/

ACCO ENTRY_DAT     AMOUNT    BALANCE
---- --------- ---------- ----------
1000 01-JAN-00         50         50
1000 02-JAN-00         40         90
1000 03-JAN-00        -50         40
1000 04-JAN-00         10         50
1000 05-JAN-00       -250       -200
1000 06-JAN-00        100       -100
1000 07-JAN-00         50        -50
2000 01-JAN-00         30         30
2000 02-JAN-00         10         40
2000 03-JAN-00       -520       -480
2000 04-JAN-00        140       -340

ACCO ENTRY_DAT     AMOUNT    BALANCE
---- --------- ---------- ----------
2000 05-JAN-00         -4       -344
2000 06-JAN-00        120       -224
2000 07-JAN-00         57       -167

14 rows selected.

SQL>

SY.

Marked as Answer by Ramky99 · Sep 27 2020

Centinul

Karthick --

I don't believe the correct results are returned with that update statement. This is what I got:

ACCO ENTRY_DATE      AMOUNT    BALANCE
---- ----------- ---------- ----------
1000 01-JAN-2000         50         50
1000 02-JAN-2000         40         40
1000 03-JAN-2000        -50        -50
1000 04-JAN-2000         10         10
1000 05-JAN-2000       -250       -250
1000 06-JAN-2000        100        100
1000 07-JAN-2000         50         50
2000 01-JAN-2000         30         30
2000 02-JAN-2000         10         10
2000 03-JAN-2000       -520       -520
2000 04-JAN-2000        140        140
2000 05-JAN-2000         -4         -4
2000 06-JAN-2000        120        120
2000 07-JAN-2000         57         57

I believe this is due to the way a correlated subquery executes.

Solomon Yakobson

Will not work - analytic functions are applied after where clause. You would need another level of inline view:

update  adam_test1 t1
   set  balance = (
                   select  balance
                     from  (
                            select  t2.rowid rid,
                                    sum(t2.amount) over(partition by t2.account order by t2.entry_date) balance
                              from  adam_test1 t2
                           )
                     where t1.rowid = rid
                   )
/

SY.

Karthick2003

Oops i should bring the summing one level down

update adam_test1 t1
   set balance = (select val 
                    from (select rowid rid, sum(balance) over(partition by account order by entry_date) val
                            from adam_test1) t2
                   where t1.rowid = t2.rid)

1 person found this helpful

Ramky99

Thanks to everyone who posted .. loads of options there, testing them out now.

Cheers,
Adam

Ramky99

I've hit a major issue with performance on this script ... table is just too big to do it in one hit.

How would I rewrite it to only consider 100 accounts at a time or something to reduce the load on the DB?

Thanks!
Adam

Tubby

As Frank pointed out earlier, it's really not a great idea to be storing this information. Do you have (as part of 'your task to add this column' a task to set up the system to properly maintain this column?) If not, then your data is going to be 'corrupt' reasonably quick. And as you're finding out, running a script to adjust the data on a regular basis isn't going to be a good idea.

If you're still set on doing this, then breaking up the records into manageable chunks as you've requested in your other thread that links to this should be a simple TOP-N query.

select 
   account_number
from
(
   select 
      account_number,
      row_number() over (partition by account_number order by account_number asc) as rn
   from YOUR_TABLE
   where balance is null -- this will omit any previously calculated records
)
where rn <= 100

Would get you the 'first' 100 records to process.

Aketi Jyuuzou

DB2 V9.5 allows below OLAP usages ;-)

-- usage1
update adam_test1 t1
   set balance = sum(balance) over(partition by account order by entry_date);

-- usage2
update (select balance,
        sum(balance) over(partition by account order by entry_date) as willSet
          from adam_test)
set balance = willSet;

I hope that Analysis Function 2.0 allows above OLAP usage.
Please test people who has Oracle11gR2 :-)

Ramky99

Hi all,

Sorry to drag this one back up but I've hit an issue with the script .. if the entry_dates are identical the running total doesn't calclulate properly.

Data before script looks like this:

ACCO ENTRY_DATE     AMOUNT    BALANCE
---- ---------- ---------- ----------
1000 01-01-2000         50
1000 01-01-2000         50
1000 02-01-2000         40
1000 03-01-2000        -50
1000 04-01-2000         10
1000 04-01-2000         10
1000 05-01-2000       -250
1000 06-01-2000        100
1000 07-01-2000         50

2000 01-01-2000         30
2000 02-01-2000         10
2000 03-01-2000       -520
2000 04-01-2000        140
2000 04-01-2000        140
2000 05-01-2000         -4
2000 06-01-2000        120
2000 07-01-2000         57

After running the script I get:

ACCO ENTRY_DATE     AMOUNT    BALANCE
---- ---------- ---------- ----------
1000 01-01-2000         50        100
1000 01-01-2000         50        100
1000 02-01-2000         40        140
1000 03-01-2000        -50         90
1000 04-01-2000         10        110
1000 04-01-2000         10        110
1000 05-01-2000       -250       -140
1000 06-01-2000        100        -40
1000 07-01-2000         50         10

2000 01-01-2000         30         30
2000 02-01-2000         10         40
2000 03-01-2000       -520       -480
2000 04-01-2000        140       -200
2000 04-01-2000        140       -200
2000 05-01-2000         -4       -204
2000 06-01-2000        120        -84
2000 07-01-2000         57        -27

The ending balance is still correct but it doesn't run right where the dates are duplicated. The true dates in the DB all include hh:mm:ss and are truly identical timestamps. Is there a way to fix this so that the balance flows correctly?

The script I used was:

merge
  into  adam_test1 t1
  using (
         select  rowid,
                 sum(amount) over(partition by account order by entry_date) balance
           from  adam_test1
        ) t2
  on (t1.rowid = t2.rowid)
  when matched
    then
      update set t1.balance = t2.balance
/

..and I'm not calculating this dynamically as the data will be static once the balances are calculated so it'll be a one shot deal.

Thanks!
Adam

21205

Monty77 wrote:
The ending balance is still correct but it doesn't run right where the dates are duplicated. The true dates in the DB all include hh:mm:ss and are truly identical timestamps. Is there a way to fix this so that the balance flows correctly?

Add something unique to it, like ROWID ..

partition by account order by entry_date, rowid

... not tested...

Aketi Jyuuzou

partition by account order by entry_date
means partition by account order by entry_date range unbounded preceding

Therefore you should use
partition by account order by entry_date rows unbounded preceding

:D

1 - 14

Locked Post

New comments cannot be posted to this locked post.

Locked on Dec 26 2009

Added on Nov 18 2009

#identity-management, #identity-manager

11 comments

1,293 views

Security Software

GTC Page Error and Nexaweb Error

Comments

Post Details