Skip to Main Content
Forums

SQL & PL/SQL

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.

how to sum on condition

687347Mar 28 2009 — edited Mar 29 2009
Hello,
I have the following data:
INSTRUMENT_TYPE INSTRUMENT_CODE DESCRIPTION TRADE_DATE  QUANTITY OPEN_CONTRACTS TRADE_AMOUNT TRANSACTION_ID 
fceu            USDEUR QSA2     USD/EUR     10.11.2008   1700000        1700000  -1323575.21          1819 
fceu            USDEUR QSA2     USD/EUR     25.11.2008  13300000       15000000 -10233130.72          1882 
fceu            USDEUR QSA2     USD/EUR     28.11.2008 -15000000              0  11598237.07          1908 
fceu            USDEUR QSA2     USD/EUR     04.02.2009    283000         283000   -218456.91          2141 
fceu            USDEUR QSA2     USD/EUR     11.02.2009    215000         498000   -166215.69          2185
where OPEN_CONTRACTS is a running total of QUANTITY.
I have to SUM(TRADE_AMOUNT), but the sum must reset whenever OPEN_CONTRACTS goes to 0.
Is this possible without writing an external procedure ?

TIA
claudio

Comments

Pravin Takpire

There would be no logs for normal transactions. All other transactions like adding tablespace/dropping tablespaces will be recored in alert_SID.log and it would be in DIAGNOSTIC_DEST directory.

regards

Pravin

unknown-951199 
2841596 wrote:




I'm learning about oracle database and I created a user and started creating tables, doing queries on Oracle Database 12c. I would like to know where can I see the transaction log, so I can see what's happening, the access and stuff. I can't find the log file anywhere. Where is it located?

does  not exist.

why do you think it should exist?

Martin Preiss

maybe it's a question of terminology: Oracle has redo log files with information on data changes und undo tablespaces containing undo records to undo changes that were made to the database by the uncommitted transaction. Some other databases (for example MS SQL Server) use a transaction log file to store these information.

ddf_dba

To actually read through a redo log you'll need to use DBMS_LOGMNR.  Read here:

https://dfitzjarrell.wordpress.com/2008/10/31/workin-in-the-mines/

David Fitzjarrell

23ai

As said earlier about the actual block level changes  are recorded into redo logs and undo data go to undo tablespace.

You can monitor live queries under v$transaction, and what you see there is sql queries submitted by the users.

Fastfwd75 
sol.beach wrote:






2841596 wrote:




I'm learning about oracle database and I created a user and started creating tables, doing queries on Oracle Database 12c. I would like to know where can I see the transaction log, so I can see what's happening, the access and stuff. I can't find the log file anywhere. Where is it located?




does  not exist.




why do you think it should exist?

I remember looking for this too when I first came to Oracle. It really should exist even if only as an optional opt-in parameter.

unknown-951199 
Fastfwd75 wrote:








I remember looking for this too when I first came to Oracle. It really should exist even if only as an optional opt-in parameter.

Have YOU submitted Enhancement Request for this?

How is this different from enabling SQL_TRACE?

Fastfwd75 
sol.beach wrote:






Fastfwd75 wrote:








I remember looking for this too when I first came to Oracle. It really should exist even if only as an optional opt-in parameter.










Have YOU submitted Enhancement Request for this?


How is this different from enabling SQL_TRACE?

Be Zen sol.beach; this is not an attack on Oracle or on you

This is different from SQL_TRACE because simply logging the SQL statements has much less overhead. I have not submitted it an an enhancement request but it's really not a novel idea. Must be a reason why Oracle does not do it but I don't know what it is.

Look up MySQL general query log to see what I mean.

1 - 8
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 26 2009
Added on Mar 28 2009
8 comments
15,319 views