Solved

Oracle - Query issue

Posted on 2014-02-20

1 solution

Medium Priority

320 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

Comment

Question by:Wilder1626

4 Comments

LVL 8

Accepted Solution

by:Surrano

Surrano earned 2000 total points

ID: 398761572014-02-21

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

LVL 11

Author Closing Comment

by:Wilder1626

ID: 398765192014-02-21

This is perfect

Thanks for your help

LVL 36

Expert Comment

by:johnsone

ID: 398765442014-02-21

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.

LVL 11

Author Comment

by:Wilder1626

ID: 398767772014-02-21

Hi johnsone

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

Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment Already a member? Login.

UNDO AND REDO IN ORACLE

Article by: Anjugarg66

This post first appeared at Oracleinaction (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…

Oracle Database

Five Myths of Oracle Database Backup

Article by: Acronis

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.

Dealing with Dates in Oracle Database

Video by: Steve

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.