Solved

Oracle SQL Select Statement

Posted on 2016-09-29
19
58 Views
Last Modified: 2016-10-03
I've got two tables in an ORA DB. One is called WORKORDER and the other is called WOSTATUS. They have a one to many relationship. For every row in the WORKORDER table, there is at least one row in the WOSTATUS table. The way the application works is that every time that you modify the status field of the work order, the application inserts a row into the WOSTATUS table. The tables have a number of columns but the ones the "connect" the two together are:

WONUM and SITEID.

The query that I'm attempting to write is one that finds WORKORDERs that have a status of 'INPRG' and where the previous status for those WORKORDERs, which you would find in the WOSTATUS table, is 'WMATL'.

I've attached an example to show you data in both tables and the result set I'm looking for.

I need to do this as a subselect with the subselect looking with the select looking for WORKORDERs with a status of 'INPRG' but then with the "subselect" limiting the result set to only those where the 2nd most recent WOSTATUS row is 'WMATL'.

I hope this make's sense.
Example-Tables-and-Result-Set.xlsx
0
Comment
Question by:cbridgman
  • 10
  • 7
  • 2
19 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41822668
sample data and expected resultLAG() OVER() allows you to get the "previous" status value on each row, after that it is just matter of using filtering criteria
SELECT
      w.wonum
    , w.siteid
    , w.changedate
    , s.status
FROM (
      SELECT
            wonum
          , siteid
          , status
          , LAG(status, 1) OVER (PARTITION BY wonum, siteid ORDER BY statusdate DESC) lagstatus
      FROM wostatus
      ) s
INNER JOIN workorder w ON s.wonum = w.wonum
            AND s.siteid = w.siteid
WHERE s.lagstatus = 'WMATL'
      AND s.status = 'INPRG'
;

Open in new window

note it is the DESCending order of statusdate locates the "previous"
0
 

Author Comment

by:cbridgman
ID: 41822690
This is close but it is not selecting only those WORKORDERs where status = 'INPRG'. Instead, it appears to return any work order that has 2 consecutive, related WOSTATUS records where the latter of the two is 'INPRG' and the one right before it is 'WMATL'

I've included an example on the spreadsheet - I've added Work Order 006 to illustrate. That one should not be in the result set but when I run the SQL that you provided it appears in the result set.

Thanks for your help, by the way
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41822714
I've included an example on the spreadsheet..... my spreadsheet isn't linked to yours :)

can you just post the sample data like this?
WOSTATUS
WONUM	SITEID	STATUS	STATUSDATE
001	ABC	WAPPR	2016-09-01
002	ABC	WAPPR	2016-08-01
002	ABC	APPR	2016-08-02
002	ABC	WMATL	2016-08-20
002	ABC	INPRG	2016-09-01
003	ABC	WAPPR	2016-08-10
003	ABC	APPR	2016-08-20
003	ABC	INPRG	2016-09-03
004	ABC	WAPPR	2016-08-22
004	ABC	APPR	2016-09-04
005	ABC	WAPPR	2016-08-01
005	ABC	APPR	2016-08-02
005	ABC	WMATL	2016-08-20
005	ABC	INPRG	2016-09-05

Open in new window

WORKORDER 
WONUM	SITEID	STATUS	CHANGEDATE
001	ABC	WAPPR	2016-09-01
002	ABC	INPRG	2016-09-02
003	ABC	INPRG	2016-09-03
004	ABC	APPR	2016-09-04
005	ABC	INPRG	2016-09-05

Open in new window

expected result:
WONUM	SITEID	CHANGEDATE
002	ABC	2016-09-02
005	ABC	2016-09-05

Open in new window

note also dates in yyyy-mm-dd make sense to the whole world (i'm not a citizen of the USA)
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41822718
try this:
SELECT
      w.wonum
    , w.siteid
    , w.changedate
    , w.status
FROM (
      SELECT
            wonum
          , siteid
          , status
          , LAG(status, 1) OVER (PARTITION BY wonum, siteid ORDER BY statusdate DESC) lagstatus
      FROM wostatus
      ) s
INNER JOIN workorder w ON s.wonum = w.wonum
            AND s.siteid = w.siteid
WHERE s.lagstatus = 'WMATL'
      AND s.status = 'INPRG'
      AND w.status = 'INPRG'
;

Open in new window

0
 

Author Comment

by:cbridgman
ID: 41822720
Oops, forgot to attach the revised sheet. Here it is
Example-Tables-and-Result-Set.xlsx
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 41822728
sample data and expected resultall that is needed I believe is the extra where condition (last line)
SELECT
      w.wonum
    , w.siteid
    , w.changedate
    , w.status
FROM (
      SELECT
            wonum
          , siteid
          , status
          , LAG(status, 1) OVER (PARTITION BY wonum, siteid ORDER BY statusdate DESC) lagstatus
      FROM wostatus
      ) s
INNER JOIN workorder w ON s.wonum = w.wonum
            AND s.siteid = w.siteid
WHERE s.lagstatus = 'WMATL'
      AND s.status = 'INPRG'
      AND w.status = 'INPRG'
;

Open in new window


nb: I'm not advocating for the use of images (which I have used to help subsequent readers if any)

I truly thank you for providing "sample data" and "expected result"
0
 

Author Comment

by:cbridgman
ID: 41822732
Your solution almost worked but I had to remove the "AND s.status = 'INPRG' " from the where clause. When I did that, it ran like a charm. Still have to create some additional test workorders to make sure but the 4 that I already created work correctly
0
 

Author Comment

by:cbridgman
ID: 41823440
may I ask 1 more question? Will the SQL that you provided me work with a SQL Server database? If not, can you tell me what I need to change?
0
 
LVL 31

Expert Comment

by:awking00
ID: 41823523
LEAD/LAG were introduced in sql server 2012, so PortletPaul's code should work for that version. There are a number of workarounds for accomplishing the same thing for earlier versions. Just Google lag function in sql server 2008 (or whatever version) and you should find them.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:cbridgman
ID: 41823537
That helps a lot since the DB that I will need to use it on in the near future is SQL Server 2012

Thanks for the info.

I need to ask 1 last question because I want to be sure that points get distributed fairly.

As PortletPaul answered the bulk of the question for me, he should obviously get the bulk of the points. But you helped too with your answer so you deserve points. I have no idea what would be fair or better stated, I want to make sure that whatever I do choose is fair to both of you.

Any suggestions?
0
 
LVL 31

Expert Comment

by:awking00
ID: 41823603
Please don't give me any points. Portlet Paul provided you with the solution and deserves the points. I just happened to know sql server introduced the lag function in sql server 2012. A simple web search would have easily discovered that. :-)
0
 

Author Comment

by:cbridgman
ID: 41823630
thanks awking00, that makes things much easier. I appreciate your help.
0
 

Author Closing Comment

by:cbridgman
ID: 41823633
Thanks very much for your help on this.
0
 

Author Comment

by:cbridgman
ID: 41823777
Maybe I should post another question for this but it is very closely related to the original.

Instead of looking at two tables in the query (WORKORDER and WOSTATUS), it dawned on me that I could really just look at the WOSTATUS table and get the same answer. In that query, Since one workorder can have many related rows in the row status table we would look in the WOSTATUS table for any workorder where the most recent status is INPRG. When we found that WOSTATUS record, we would then look at the 2nd most recent to find if the status of the record is WMATL. If it is, then we found a work order that meets our condition. If the most recent WOSTATUS record for a workorder is INPRG and the 2nd most recent record for that same work order is not WMATL, that one wouldn't be included in the result set.

Does that make sense. The attachments that I have provide can pretty much illustrate this.

Can you help with the SQL for this?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41824330
Lol. I originally used the single table approach then realised you had asked for the date field from wostatus in the result
And that date is different in your sample data.

Then you added extra where clause conditions that required the wostatus table.

Are you certain you want just the one table?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41825145
This is what a singe table version would look like
SELECT
      wonum
    , siteid
    , statusdate
    , status
FROM (
      SELECT
            wonum
          , siteid
          , statusdate
          , status
          , LAG(status, 1) OVER (PARTITION BY wonum, siteid ORDER BY statusdate DESC) lagstatus
      FROM wostatus
    ) 
WHERE lagstatus = 'WMATL'
      AND status = 'INPRG'
;

Open in new window

note it is the DESCending order of statusdate locates the "previous"
0
 

Author Comment

by:cbridgman
ID: 41827382
hi again portlet paul

the single table query is close but not quite. I only want the result set to include a work order if it's most recent status is INPRG and the status just before was WMATL. In the following example, the result set should not include wonum 12345 because its most recent status record is COMP. It should, however, return wonum 45678 because its most recent status record is INPRG. When I run the SQL that you have just provided, it returns both

wonum     status     date
-----------     ---------    ----------------
12345        comp     2016-09-10
12345        inprg      2016-09-01
12345        wmatl    2016-08-07
12345        wappr    2016-08-05
45678        inprg      2016-09-01
45678        wmatl    2016-08-07
45678        wappr    2016-08-05
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41827436
Please try this and let me know.
SELECT
      wonum
    , siteid
    , statusdate
    , status
FROM (
      SELECT
            wonum
          , siteid
          , statusdate
          , status
          , LAG(status, 1) OVER (PARTITION BY wonum, siteid ORDER BY statusdate DESC) lagstatus
          , row_number() OVER (PARTITION BY wonum, siteid ORDER BY statusdate DESC) RN
      FROM wostatus
    ) 
WHERE lagstatus = 'WMATL'
      AND status = 'INPRG'
      AND RN = 1
;

Open in new window

0
 

Author Comment

by:cbridgman
ID: 41827442
No records were returned that time and I made sure that one work order in the db met the criteria.

It's status changes, in order were

WAPPR
APPR
WMATL
INPRG

It started out as WAPPR and its most recent is INPRG

I think we are getting close though :-)
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
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
Via a live example, show how to take different types of Oracle backups using RMAN.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now