 Questions about conditions depending values etc.. [message #916805] |
Wed, 19 September 2012 13:42  |
 Blaine Mising name
Messages: 25
Registered: December 2010 |
Junior Member |
|
|
I want to do a report that shows all the changes of a procut. The product will be asked in a parameter window.
I've a database in Oracle 11. I will simplify the tables.
The table "PRODUCT" has this columns: Product_id (key column), description, price, quantity
This table contains all the products
Example of data:
---------- ------------- -------- ----------
PRODUCT_ID DESCRIPTION PRICE quantity
---------- ------------- -------- ----------
1 product 1 10 1
2 product 2 500 10
---------- ------------- -------------------
All the changes made in this table are stored in a table called H_PRODCUT
H_PRODUCT table has this columns: Operation, date, product_id, event_id, field, value.
Operation = I (Insertion), U (is an Update), D ( the product is Deleted)
Date = Date and time of the operation.
procuct_id = the id of the product.
field = contains the field changed in an operation. If field is not
updated, it is not registered.
value = New value of the field.
event_id = if more than one field is modified (description, price, quantity) there will be more than one register with the same product_id and event_id, because they are updated in the same operation, if not each operation has his own event_id.
Data example:
---------- ---------------- -------------- ---------- ----------- -----------
Operation date product_id event_id field value
---------- ---------------- -------------- ---------- ------------ ----------
I 2012-03-10 01:00 1 1 description product1
I 2012-03-10 01:00 1 1 price 10
I 2012-03-10 01:00 1 1 quantity 0
M 2012-03-10 01:05 1 2 description produ1
M 2012-03-10 12:05 1 3 description prod1
M 2012-03-10 12:05 1 3 price 15
M 2012-03-10 12:05 1 3 quantity 1
D 2012-03-10 01:05 1 4 product_id 1
Here is an output example of the report that I want to do:
---------- ---------------- -------------- ------------- --------- ----------
Operation date product_id description price Quantity
---------- ---------------- -------------- ------------- --------- ----------
I 2012-03-10 01:00 1 product1 10 0
M 2012-03-10 01:05 1 produ1 10 0
M 2012-03-10 12:05 1 prod1 15 1
D 2012-03-10 13:25 1 prod1 15 1
When a product is new, H_PRODUCT will have a register for each field. Then I've all the information.
When a product is updated, H_PRODUCT will have only an operation field for the fields modified. Not all fields and values are stored.
When a product is deleted H_PRODUCT will have only an operation register with one field.
The problem is when a product is updated because only the new fields/values are stored.
I know that it's possible obtain the info with SQL but, I think that this is not very clean. it's possible do it doing something like this?
For example:
1. Do a DataSet, getting the insertion data and store the values of all the current fields in global variables
2. Do a DataShet getting the updated data and check with birt if there is a register with the field, if not put in the report the current value of the global variable for the field. If the field is in datashet store the new value in the global variable and put in the report the new value.
3. Do a DataShet getting the deleted data of the product. Get the fields values as in second step.
It's possible doing this with conditions? How Can I do them?
[Updated on: Wed, 19 September 2012 14:31] Report message to a moderator |
|
|
|
|
Powered by
FUDForum. Page generated in 0.04347 seconds
] 
Search
Help
Register
Login
Home
