Google Answers Logo
View Question
 
Q: Oracle Query help ( No Answer,   0 Comments )
Question  
Subject: Oracle Query help
Category: Computers > Programming
Asked by: mammens-ga
List Price: $15.00
Posted: 06 Nov 2006 12:36 PST
Expires: 08 Nov 2006 10:08 PST
Question ID: 780578
LAST_EXPORTED_DATE  = 11/3/06
=============================

Table Person
------------------------------
id	dob	name	tstamp
-------------------------------
1	2/1/50	bob	11/6/06 -- dob got updated from 1/1/50 to 2/1/50
2	1/1/70	chuck	11/6/06 -- name got updated from chuk to chuck

Table Person_Audit
--------------------------------------
pid	id	dob	name	tstamp
--------------------------------------
1	1	1/1/50	bob	1/1/06		
2	1	2/1/50	bob	11/6/06
3	2	1/1/70	chuk	1/1/06
4	2	1/1/70	chuck	11/6/06


id:1 dob is changed from 1/1/50 to 2/1/50   -- on 11/6/06
id:2 name is changed from chuk to chuck     -- on 11/6/06


Query output (old record and new record where the dob is changed ONLY)
------------------------
id	dob	name
------------------------
1	1/1/50	bob	-- Old
1	2/1/50	bob	-- New

The id:2 record should not be returned since the dob is not changed
and does not need to report though it is available in audit table.

The exported date is used to see when it was last exported to client.
So in this case, the last export was on 11/3 and so any change after that
needs to be exported again. The OLD record would be the last exported data
to client (max tstamp <= LAST_EXPORTED_DATE)


NOW, similarly there is one more

Table C
------------------------------
id	name	age	tstamp
------------------------------
10	smythe	44	11/6/06  -- name got updated from smith to smythe
20	jones	20	11/6/06  -- age got updated from 20 to 21

Table C_Audit
---------------------------------------
cid	id	name	age	tstamp
----------------------------------------
1	10	smith	44	1/1/06
2	10	smythe	44	11/6/06
3	20	jones	30	1/1/06
4	20	jones	31	11/6/06

Query result (old record and new record where the name is changed ONLY)
-----------------------------------------------------------------------
id	name
10	smith
10	smythe

The id:20 should not be returned since the name is not changed and does not
need to report though it is in audit table.

Again as in the earlier case, only those changes after the LAST_EXPORTED_DATE
is only needed in the output.


FINAL RESULT from both the 2 results above as ONLY 1 record (asof
LAST_EXPORTED_DATE)
====================================================================================
id	dob	name
-----------------------
1	1/1/06	null		--> here the dob is different
1	2/1/06	null
10	null	smith		--> here the name is different
10	null	smythe
Answer  
There is no answer at this time.

Comments  
There are no comments at this time.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  


Google Home - Answers FAQ - Terms of Service - Privacy Policy