?
Solved

SQL command to find the last record on an audit table Crystal report Oracle 10g

Posted on 2011-05-06
12
Medium Priority
?
565 Views
Last Modified: 2013-12-18
Hello,

I am writing a SQL command report on an Oracle 10g database.  The table tracks audits of changes to material's management inventory item records.  The table has almost two million records.  I would like your input on how to write an efficient query to find the last record updated for an item.
0
Comment
Question by:jrbledsoe001
  • 7
  • 5
12 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35706932
can crystal use the data warehouse functions?

select column from (
select column, row_number() over(partition by item order by some_date desc) myRowNum
)
where myRowNum=1
0
 

Author Comment

by:jrbledsoe001
ID: 35706948
Here is my SQL statement:
select
iau.item_group,    
iau.company,        
iau.location,      
iau.item,          
iau.r_date,        
iau.r_time,        
iau.fld_nbr,        
iau.before_image,
iau.after_image,
iau.operator_id,        
idi.fld_name
from lsfmigd.icaudit iau, lsfmigd.icdict idi
where iau.fld_nbr = idi.fld_nbr and
iau.item_group = 'HOSP' and
iau.company >= 0 and
iau.location = '     ' and      
iau.item='{?Item}';  

notes
 '{?Item}' is a SQL command parameter
I used the 'explain plan for' Oracle feature to create a query which uses the most efficient index columns
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35706968
Just add the row_number() call to that select, then wrap an outer select around that with the where clause I provided.

I can't test it but something like below.  I guessed at ordering by r_date.
select
item_group,     
company,        
location,       
item,           
r_date,         
r_time,         
fld_nbr,        
before_image,
after_image,
operator_id,        
fld_name
from 
(
select
iau.item_group,     
iau.company,        
iau.location,       
iau.item,           
iau.r_date,         
iau.r_time,         
iau.fld_nbr,        
iau.before_image,
iau.after_image,
iau.operator_id,        
idi.fld_name,
row_number() over(partition by iau.item order by iau.r_date desc) myRowNum
from lsfmigd.icaudit iau, lsfmigd.icdict idi
where iau.fld_nbr = idi.fld_nbr and
iau.item_group = 'HOSP' and
iau.company >= 0 and 
iau.location = '     ' and       
iau.item='{?Item}'
)
where myrownum=1;

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35706980
I also suppose you could just use the MAX call and group by all the other columns.
0
 

Author Comment

by:jrbledsoe001
ID: 35706987
Here is my output

iau-ITEMAUDIT-sql.pdf
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35706996
You can add the r_time to my order.  You also have the option of converting the date and time columns into an Oracle date data type and use that.

Look up the TO_DATE function if you are interested.
0
 

Author Comment

by:jrbledsoe001
ID: 35707018
I need help modifying the SQL so that the query locates only the last records updated by date and time.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35707029
What about what I posted in http:#35706968?

just change:
row_number() over(partition by iau.item order by iau.r_date desc) myRowNum

to
row_number() over(partition by iau.item order by iau.r_date desc, iau.r_time desc) myRowNum
0
 

Author Comment

by:jrbledsoe001
ID: 35709134
Hi slightwv,

I have to research your solution to have a better understanding.  I'm new to SQL Plus and don't understand yet what your code is doing.  I'll try it and let you know how it goes.  Thanks for posting so quickly to my question.
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 35709218
The row_number() function is a data warehouse windowing function.  These types of functions are pretty powerful.  I suggest you spend a little time in the docs.

They can probably explain them better than I can here:

http://download.oracle.com/docs/cd/E11882_01/server.112/e16579/analysis.htm
0
 

Author Comment

by:jrbledsoe001
ID: 35717237
BRAVO GENIUS!!!  This works perfectly.  Thank you for your help
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35717242
No problem.  Glad it worked for you.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question