[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Oracle SQL Select Statement

Posted on 2016-09-29
19
Medium Priority
?
122 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
[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
  • 10
  • 7
  • 2
19 Comments
 
LVL 49

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 49

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 49

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 49

Accepted Solution

by:
PortletPaul earned 2000 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 32

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
 

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 32

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 49

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 49

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 49

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
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.
Video by: Steve
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…

649 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