Solved

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

Posted on 2011-05-06
12
555 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
12 Comments
 
LVL 77

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 77

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 77

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 77

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 77

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 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 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 77

Expert Comment

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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

726 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