Solved

Last record chosen in Oracle Query

Posted on 2016-10-19
3
78 Views
Last Modified: 2016-10-20
I need to only retrieve the last record when this Oracle 9.2 query executes.

DI  2016-09-26  168398    063848979   //SRC RMS


I would also like another Oracle query that only retrieves the first record.

NULL      DI  2016-09-16  174261    063848979   //SRC RMS             FB BRAMPTON TO

Right now,  the query will retrieve 3 records for this problem when it executes.
The 1993237 will be a field once I get it to work.
This Oracle query executes inside a SQL query.




SELECT (CASE WHEN TO_CHAR(aa.NOTES)  IS NOT NULL  THEN  TO_CHAR(aa.NOTES)  ELSE TO_CHAR(a.PTEXT) END) as MyNote
FROM            GPCOMP1.GPPROB a, GPCOMP1.GPPROBNOTES aa
WHERE        a.PROBLEM_ID = aa.PROB_ID AND (a.PROBLEM_ID = 1993237)
ORDER BY a.MODIFIED_ON DESC




 Oracle Query
0
Comment
Question by:thayduck
[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
  • 2
3 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 41850949
First row:
select mynote from (
SELECT (CASE WHEN TO_CHAR(aa.NOTES)  IS NOT NULL  THEN  TO_CHAR(aa.NOTES)  ELSE TO_CHAR(a.PTEXT) END) as MyNote,
row_number() over(order by modified_on asc) rn
 FROM            GPCOMP1.GPPROB a, GPCOMP1.GPPROBNOTES aa
 WHERE        a.PROBLEM_ID = aa.PROB_ID AND (a.PROBLEM_ID = 1993237)
)
where rn=1


Last row:
select mynote from (
SELECT (CASE WHEN TO_CHAR(aa.NOTES)  IS NOT NULL  THEN  TO_CHAR(aa.NOTES)  ELSE TO_CHAR(a.PTEXT) END) as MyNote,
row_number() over(order by modified_on desc) rn
 FROM            GPCOMP1.GPPROB a, GPCOMP1.GPPROBNOTES aa
 WHERE        a.PROBLEM_ID = aa.PROB_ID AND (a.PROBLEM_ID = 1993237)
)
where rn=1


I'm guessing that really isn't what you want.  Can you provide some sample data and exepected results?
0
 

Author Comment

by:thayduck
ID: 41852218
Your code did exactly what I wanted.

 I did have to change :

 row_number() over(order by modified_on desc) rn

 TO

 row_number() over(order by a.modified_on desc) rn


 Thank you for the quick response.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41852228
No problem.  Glad to help.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle Errors 11 88
Oracle - BLOB Extract Line 2 16
Oracle cluster . 1 24
Force XMLSEQUENCE to return empty tags for null values. 10 45
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…
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 how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

734 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