Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.
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?
Active 5 days ago
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
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…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax:
(CODE)
This way, I guessed, I would ensure that use…
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 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.
Suggested Courses
Course of the Month4 days, 12 hours left to enroll
661 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.