Solved

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

Posted on 2011-05-06
12
550 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 76

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 76

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

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 76

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 76

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 76

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 76

Expert Comment

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

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

815 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now