1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Need SQL query

Discussion in 'SQL PL/SQL' started by ajay696, Jun 24, 2013.

  1. ajay696

    ajay696 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hi All,

    Workorder table:

    wonum status changedate

    1234 CRCOMP 12-jun-13
    1324 APPR 11-jun-13
    3265 CRCOMP 12-jun-13
    5431 APPR 11-jun-13
    6532 INPRG 12-june-13
    5421 CRCOMP 13-june-13

    WOSTATUS table:

    wonum status changedate
    1234 APPR 11-jun-13
    1234 CRCOMP 12-june-13
    1324 APPR 11-june-13
    3265 APPR 11-june-13
    3265 CRCOMP 12-june-13
    5431 APPR 11-june-13
    6532 APPR 10-june-13
    6532 INPRG 12-june-13
    5421 APPR 10-june-13
    5421 INPRG 11-june-13
    5421 CRCOMP 13-june-13


    Actually the user have to change appr to inprg and next crcomp by mistake user directly changed to crcomp status from APPR status. see above records.

    What is the sql query to identify the records which are changed from APPR to CRCOMP... on Workorder table

    Any help on sql query to fetch the work orders from work order table which are changed from APPR to CRCOMP. because we need to set status back to APPR for those records

    any help would be greatly appreciated.


    Thanks,
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I am basing this on assumptions made from the sample data provided.

    It appears that to reach the CRCOMP status an item must have a starting status of APPR, then move to a status of INPRG. Given that as true any records in workorder that went directly from a status of APPR to CRCOMP must have been updated in error. It's fairly straightforward to return the incorrectly updated records from the WOSTATUS table and apply the proper updates to WORKORDER:

    Code (SQL):
    SQL> CREATE TABLE workorder(
      2          wonum NUMBER NOT NULL,
      3          STATUS  varchar2(8) NOT NULL,
      4          changedate DATE NOT NULL
      5  );
     
    TABLE created.
     
    SQL>
    SQL> CREATE TABLE wostatus(
      2          wonum NUMBER NOT NULL,
      3          STATUS varchar2(8) NOT NULL,
      4          changedate DATE NOT NULL
      5  );
     
    TABLE created.
     
    SQL>
    SQL> INSERT ALL
      2  INTO workorder
      3  VALUES(1234, 'CRCOMP', '12-jun-13')
      4  INTO workorder
      5  VALUES(1324, 'APPR', '11-jun-13')
      6  INTO workorder
      7  VALUES(3265, 'CRCOMP', '12-jun-13')
      8  INTO workorder
      9  VALUES(5431, 'APPR', '11-jun-13')
     10  INTO workorder
     11  VALUES(6532, 'INPRG', '12-june-13')
     12  INTO workorder
     13  VALUES(5421, 'CRCOMP', '13-june-13')
     14  INTO wostatus
     15  VALUES(1234,'APPR','11-jun-13')
     16  INTO wostatus
     17  VALUES(1234,'CRCOMP','12-june-13')
     18  INTO wostatus
     19  VALUES(1324,'APPR','11-june-13')
     20  INTO wostatus
     21  VALUES(3265,'APPR','11-june-13')
     22  INTO wostatus
     23  VALUES(3265,'CRCOMP','12-june-13')
     24  INTO wostatus
     25  VALUES(5431,'APPR','11-june-13')
     26  INTO wostatus
     27  VALUES(6532,'APPR','10-june-13')
     28  INTO wostatus
     29  VALUES(6532,'INPRG','12-june-13')
     30  INTO wostatus
     31  VALUES(5421,'APPR','10-june-13')
     32  INTO wostatus
     33  VALUES(5421,'INPRG','11-june-13')
     34  INTO wostatus
     35  VALUES(5421,'CRCOMP','13-june-13')
     36  SELECT * FROM dual;
     
    17 ROWS created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> SELECT * FROM workorder;
     
         WONUM STATUS   CHANGEDAT
    ---------- -------- ---------
          1234 CRCOMP   12-JUN-13
          1324 APPR     11-JUN-13
          3265 CRCOMP   12-JUN-13
          5431 APPR     11-JUN-13
          6532 INPRG    12-JUN-13
          5421 CRCOMP   13-JUN-13
     
    6 ROWS selected.
     
    SQL> SELECT * FROM wostatus;
         WONUM STATUS   CHANGEDAT
    ---------- -------- ---------
          1234 APPR     11-JUN-13
          1234 CRCOMP   12-JUN-13
          1324 APPR     11-JUN-13
          3265 APPR     11-JUN-13
          3265 CRCOMP   12-JUN-13
          5431 APPR     11-JUN-13
          6532 APPR     10-JUN-13
          6532 INPRG    12-JUN-13
          5421 APPR     10-JUN-13
          5421 INPRG    11-JUN-13
          5421 CRCOMP   13-JUN-13
     
    11 ROWS selected.
     
    SQL>
    SQL> SELECT wonum, STATUS, changedate
      2  FROM wostatus
      3  CONNECT BY nocycle wonum = prior wonum
      4  /
     
         WONUM STATUS   CHANGEDAT
    ---------- -------- ---------
          1234 APPR     11-JUN-13
          1234 CRCOMP   12-JUN-13
          1324 APPR     11-JUN-13
          3265 APPR     11-JUN-13
          3265 CRCOMP   12-JUN-13
          5421 APPR     10-JUN-13
          5421 CRCOMP   13-JUN-13
          5421 INPRG    11-JUN-13
          5431 APPR     11-JUN-13
          6532 APPR     10-JUN-13
          6532 INPRG    12-JUN-13
     
    11 ROWS selected.
     
    SQL>
    SQL> SELECT wonum, STATUS, changedate
      2  FROM wostatus
      3  WHERE wonum IN (SELECT wonum FROM wostatus GROUP BY wonum HAVING COUNT(*) = 2)
      4  AND STATUS = 'CRCOMP'
      5  /
     
         WONUM STATUS   CHANGEDAT
    ---------- -------- ---------
          1234 CRCOMP   12-JUN-13
          3265 CRCOMP   12-JUN-13
     
    SQL>
    The connect by query was necessary to see the progression of status values for valid CRCOMP assignments. The last query shown would be the one you'd run to find the wonum records updated in error.