Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2013
    Posts
    3

    Unanswered: Help on SQL query

    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 would be greatly appreciated.


    Thanks,
    Last edited by ajay696; 06-24-13 at 09:57.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    DBMS_LOGMNR can reveals which records were changed
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2013
    Posts
    3

    Hi

    Thank you for your answer

    That's correct,but the actual requirement is we need to update status column back to APPR on work order table based on the wostatus table.

    We have to set status back to APPR for the records which are changed incorrectly from incorrectly APPR to CRCOMP.

    For example in the above table the 3265 directly changed to CRCOMP status.
    actually the records status order is APPR-INPRG-CRCOMP.

    So we need to set back the status of those records which are changed from APPR to CRCOMP.

    Any Help on sql query

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Realize we don't have your tables, data or REDO logfiles; you actually need to write the necessary SQL

    post SELECT statement that properly returns the rows which were wrongly changed.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    actually the records status order is APPR-INPRG-CRCOMP.
    find the wonum which have CRCOMP and have not INPRG in wostatus table,
    to find the wonum which are changed incorrectly from APPR to CRCOMP,

    Are always 'APPR' there for every wonum in wostatus table?
    Or, if some wonum incorrectly lack APPR, then what sould we do for the wonum in Workorder table?

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    <select wonum from Workorder wo
    where status = 'CRCOMP'
    and not exists (select 1 from WOSTATUS ws
    where wo.wonum = ws.wonum
    and ws.status = 'INPRG')

    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •