Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.
Course Of The Month
5 days, 15 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Oracle - filter sequal issue
Posted on 2012-12-20
Hello all
I have a problem with a SQL.
I have this SQL bellow that i use to pull resources from a table.
The thing is that in TL_RESOURCE_TRLR A i may have multiple A.TRLR_TYPE_ID based on A.EFFECTIVE
So i only want to extract the most updated effective date per TRLR_TYPE_ID and not all the effective dates even the old one. Just the very last one
How can i update this?
Thanks again for your help
I have a problem with a SQL.
I have this SQL bellow that i use to pull resources from a table.
The thing is that in TL_RESOURCE_TRLR A i may have multiple A.TRLR_TYPE_ID based on A.EFFECTIVE
So i only want to extract the most updated effective date per TRLR_TYPE_ID and not all the effective dates even the old one. Just the very last one
How can i update this?
Thanks again for your help
SELECT DISTINCT A.CARRIER_ID,
A.EFFECTIVE,
A.TRLR_TYPE_ID,
A.CURRENT_NUM_VEHICLES
FROM TL_RESOURCE_TRLR A, CARRIER_EQUIP B
WHERE A.CARRIER_ID = B.CARRIER_ID(+)
AND A.TRLR_TYPE_ID = B.TRLR_TYPE_ID(+)
AND B.CARRIER_ID IS NULL -- EXCLUDE ROWS ADDED BECAUSE OF THE OUTER JOIN
ORDER BY A.CARRIER_ID ASC;
[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-
2
4 Comments
Active today
Try this:
select carrier_id, effective, trlr_type_id, current_num_vehicles
from
(
SELECT A.CARRIER_ID,
A.EFFECTIVE,
A.TRLR_TYPE_ID,
A.CURRENT_NUM_VEHICLES,
row_number() over(partition by carrier_id order by carrier_id, effective desc) myrn
FROM TL_RESOURCE_TRLR A, CARRIER_EQUIP B
WHERE A.CARRIER_ID = B.CARRIER_ID(+)
AND A.TRLR_TYPE_ID = B.TRLR_TYPE_ID(+)
AND B.CARRIER_ID IS NULL -- EXCLUDE ROWS ADDED BECAUSE OF THE OUTER JOIN
)
where myrn=1;
or a simple max?
SELECT DISTINCT A.CARRIER_ID,
MAX(A.EFFECTIVE),
A.TRLR_TYPE_ID,
A.CURRENT_NUM_VEHICLES
FROM TL_RESOURCE_TRLR A, CARRIER_EQUIP B
WHERE A.CARRIER_ID = B.CARRIER_ID(+)
AND A.TRLR_TYPE_ID = B.TRLR_TYPE_ID(+)
AND B.CARRIER_ID IS NULL -- EXCLUDE ROWS ADDED BECAUSE OF THE OUTER JOIN
group by carrier_id, trlr_type_id, current_num_vehicles
ORDER BY A.CARRIER_ID ASC;
select carrier_id, effective, trlr_type_id, current_num_vehicles
from
(
SELECT A.CARRIER_ID,
A.EFFECTIVE,
A.TRLR_TYPE_ID,
A.CURRENT_NUM_VEHICLES,
row_number() over(partition by carrier_id order by carrier_id, effective desc) myrn
FROM TL_RESOURCE_TRLR A, CARRIER_EQUIP B
WHERE A.CARRIER_ID = B.CARRIER_ID(+)
AND A.TRLR_TYPE_ID = B.TRLR_TYPE_ID(+)
AND B.CARRIER_ID IS NULL -- EXCLUDE ROWS ADDED BECAUSE OF THE OUTER JOIN
)
where myrn=1;
or a simple max?
SELECT DISTINCT A.CARRIER_ID,
MAX(A.EFFECTIVE),
A.TRLR_TYPE_ID,
A.CURRENT_NUM_VEHICLES
FROM TL_RESOURCE_TRLR A, CARRIER_EQUIP B
WHERE A.CARRIER_ID = B.CARRIER_ID(+)
AND A.TRLR_TYPE_ID = B.TRLR_TYPE_ID(+)
AND B.CARRIER_ID IS NULL -- EXCLUDE ROWS ADDED BECAUSE OF THE OUTER JOIN
group by carrier_id, trlr_type_id, current_num_vehicles
ORDER BY A.CARRIER_ID ASC;
Active today
I might have fat-fingered when I retyped your SQL.
If you can provide some sample data and expected results, I can create an actual working example.
If you can provide some sample data and expected results, I can create an actual working example.
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface. I made a script to download the Alert Log, look for errors, and email me the trace files. In this article I'll describe what I did and share my script.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Course of the Month5 days, 15 hours left to enroll
Earn Certification
Certified Information Systems Auditor (CISA)
$99.00$89.10
705 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.