We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Solved
Oracle - Query issue
Posted on 2014-02-20
Hi
I have this below query that pulls data from 1 single table:
What i'm trying to do is to find every MILES_INDICATOR that equals '4' and then, it will take the LOCATION_ID of the same row as the FROM value, then take the LOCATION_ID of the following row as the TO value.
The data must be sorted as : ORDER BY LOAD_ID, STOP_NUM.
How can i do that?
In the attachment, you will see 2 sheets:
1- Table extract = an example of the data in the table: LOAD_STOP
2- Final result = what i would like to see as the final result from the query
Thanks again for your help.
Table-result.xls
I have this below query that pulls data from 1 single table:
SELECT LOAD_ID, STOP_NUM, LOCATION_ID, MILES, MILES_INDICATOR
FROM LOAD_STOP
WHERE OPEN_TIME > TO_DATE (TO_CHAR (SYSDATE, 'YYYYMMDD'), 'YYYYMMDD') - 7
ORDER BY LOAD_ID, STOP_NUM;
What i'm trying to do is to find every MILES_INDICATOR that equals '4' and then, it will take the LOCATION_ID of the same row as the FROM value, then take the LOCATION_ID of the following row as the TO value.
The data must be sorted as : ORDER BY LOAD_ID, STOP_NUM.
How can i do that?
In the attachment, you will see 2 sheets:
1- Table extract = an example of the data in the table: LOAD_STOP
2- Final result = what i would like to see as the final result from the query
Thanks again for your help.
Table-result.xls
[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
4 Comments
Something like this?
Select a.location_id "from", b.location_id "to", a.miles_indicator
from load_stop a, load_stop b
where a.OPEN_TIME > TO_DATE (TO_CHAR (SYSDATE, 'YYYYMMDD'), 'YYYYMMDD') - 7
and a.miles_indicator=4
and a.stop_num+1 = b.stop_num
and a.load_id = b.load_id
order by load_id, stop_num;
Active today
Well, I guess I was a little late.
This should do it without having to join the table to itself. Should perform much better and should handle cases where there are gaps in the STOP_NUM field.
There is no OPEN_TIME field in the sample data that you supplied, so I assume it is in that table. Also, removed the unnecessary conversions on SYSDATE.
There are also syntax errors in the accepted answer. The ORDER BY has ambiguous column names. They need to be prefixed with the correct table alias.
This should do it without having to join the table to itself. Should perform much better and should handle cases where there are gaps in the STOP_NUM field.
SELECT *
FROM (SELECT load_id,
stop_num,
location_id "FROM",
miles,
miles_indicator,
Lead(location_id)
over (
PARTITION BY load_id
ORDER BY stop_num) "TO"
FROM load_stop
WHERE open_time > Trunc(SYSDATE) - 7)
WHERE miles_indicator = 4
ORDER BY load_id,
stop_num;
There is no OPEN_TIME field in the sample data that you supplied, so I assume it is in that table. Also, removed the unnecessary conversions on SYSDATE.
There are also syntax errors in the accepted answer. The ORDER BY has ambiguous column names. They need to be prefixed with the correct table alias.
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Confronted with some SQL you don't know can be a daunting task.
It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as:
(+) as used in Oracle; *= =* as used in Sybase …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:
ht…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
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…
Suggested Courses
Course of the Month8 days, 18 hours left to enroll
Earn Certification
Certified Information Systems Auditor (CISA)
$99.00$89.10
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.