Solved

Oracle - Query issue

Posted on 2014-02-20
4
310 Views
Last Modified: 2014-02-21
Hi

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;

Open in new window


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
0
Comment
Question by:Wilder1626
[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
 
LVL 8

Accepted Solution

by:
Surrano earned 500 total points
ID: 39876157
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;

Open in new window

0
 
LVL 11

Author Closing Comment

by:Wilder1626
ID: 39876519
This is perfect

Thanks for your help
0
 
LVL 35

Expert Comment

by:johnsone
ID: 39876544
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.

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; 

Open in new window


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.
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 39876777
Hi johnsone

Thanks for still giving me some more ideas. I will test this and come back to you.
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to keep a record with the highest value 3 58
Performance Issue in Oracle 3 45
When are cursors useful? 8 60
T-SQL: need to reset a declared variable 4 31
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
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.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question