Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-05-06
12
Medium Priority
?
562 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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 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 77

Expert Comment

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

Featured Post

Industry Leaders: 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!

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

618 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