Wilder1626
asked on
Oracle - Query issue
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
Hi johnsone
Thanks for still giving me some more ideas. I will test this and come back to you.
Thanks for still giving me some more ideas. I will test this and come back to you.
ASKER
Thanks for your help