It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Last record chosen in Oracle Query
Posted on 2016-10-19
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
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
[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
Active today
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?
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?
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.
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.
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
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…
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 explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month5 days, 22 hours left to enroll
Earn Certification
Certified Information Systems Auditor (CISA)
$99.00$89.10
627 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.