Oracle SQL query to fetch data from log table

Question

The below provided data is tiny snapshot of a huge log table. Please help with me a query to identify records having the TRAN_ID's 451140014 and 440102253.

The status of the record is getting updated to 'Definite' from 'Actual'. As per the business rules of our application it is NOT suppose to happen, I need to fetch the list of all records in this huge table where the statuses are getting updated.

ROW_ID        TRAN_ID      TRAN_DATE                 CHG_TYPE    DB_SESSION    DB_OSUSER    DB_HOST     STAT_CD
500-XNEGXU    451327759    7/24/2015 11:35:26 AM    Update     SBLDATALOAD     siebelp       pas01      Actual
500-XNEGXU    451299279    7/24/2015 10:13:18 AM    Update     SBLDATALOAD     siebelp       pas01      Actual
500-XNEGXU    451140014    7/24/2015 1:04:36 AM     Update      SBLDATALOAD     siebelp      pas01      Definite
500-XNEGXU    440102253    6/23/2015 3:10:33 PM     Update      SBLDATALOAD     convteam     pas01      Actual
500-XNEGXU    426245149    5/8/2015 2:11:21 PM      Update       SBLDATALOAD     convteam    pas11      Actual

Edit : thanks a lot Ponder for your help. Little modification of your query to get the results in a single row. This would give me the next transaction id which flipped the status from 'Actual' to 'Definite'

select row_id, tran_id, next_tran_id,tran_date, next_tran_date,stat_cd
  from (
    select abc.*, lag(tran_id) over (order by tran_id desc) next_tran_id,lag(tran_date) over (order by tran_id desc) next_tran_date,
        case when stat_cd='Actual' and (lag(stat_cd) over (partition by row_id order by tran_id desc)) = 'Definite' then 1 
        end change
      from abc )
  where change = 1 order by row_id, tran_id

I don't understand what logic you want to implement. Assuming that row_id is the primary key of the actual table, I see that the stat_cd was set to "Definite" in tran_id 451140014. Why would 440102253 be returned? Do you want to return any transaction where the stat_cd is "Definite"? Or only where the prior transaction for that row_id has a stat_cd of "Actual"? — Justin Cave
– Justin Cave, Commented Jul 27, 2015 at 4:53
Sorry for not being clear, I want to return the transactions where the status was 'Actual' earlier and flipped to 'Definite' later, from the dataset I want to return these 2 transactions as the output(440102253 and 451140014) — user2999377
– user2999377, Commented Jul 27, 2015 at 13:39

Ponder Stibbons · Accepted Answer · 2015-07-27 15:57:29Z

0

This query, using function lead() displays all rows where stat_cd is Definite and prior row in order of tran_id:

select row_id, tran_id, tran_date, stat_cd 
  from (
    select data.*, 
        case when stat_cd='Definite' 
               or (lead(stat_cd) over (order by tran_id)) = 'Definite' then 1 
        end change
      from data )
  where change = 1 order by row_id, tran_id

^{SQLFiddle demo}

You may need to change over (order by tran_id) to over (partition by row_id order by tran_id) if your data is organized this way.

Edit: Modified query after additional informations were provided:

select row_id, tran_id, tran_date, stat_cd 
  from (
    select xyz.*,
        case 
          when stat_cd='Actual'   
               and (lead(stat_cd) over (order by tran_id)) = 'Definite' then 1 
          when stat_cd='Definite' 
               and (lag(stat_cd)  over (order by tran_id)) = 'Actual'   then 2 
        end change
      from xyz)
  where change is not null

^{SQLFiddle demo}

edited Jul 27, 2015 at 15:57

answered Jul 27, 2015 at 11:26

Ponder Stibbons

14.9k2 gold badges23 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user2999377 Over a year ago

Thank you for your query Ponder. When I test the query for full set of records I am not getting the desired output. I would require only 2 records with the transaction id's where the status of the record flipped from 'Actual' to 'Definite' .I have pasted the data in SQL Fiddle Demo for your reference. More over I need to find all such row_id's in a big table , Can you please help sqlfiddle.com/#!4/9eecb7d/2764

Ponder Stibbons Over a year ago

@user2999377 - Probably I can help, but your SQLFiddle link is not working. Please correct it or, more preferably, edit your original question and add there new example data and other informations.

user2999377 Over a year ago

Thanks a lot Ponder, another version of the same query which provides me output in a single row. select row_id, tran_id, next_tran_id,tran_date, next_tran_date,stat_cd from ( select abc.*, lag(tran_id) over (order by tran_id desc) next_tran_id,lag(tran_date) over (order by tran_id desc) next_tran_date, case when stat_cd='Actual' and (lag(stat_cd) over (partition by row_id order by tran_id desc)) = 'Definite' then 1 end change from abc ) where change = 1 order by row_id, tran_id

Ponder Stibbons Over a year ago

I'm glad it helped. And I see you caught it. I hope lag(), lead() and sqlfiddle will serve you well in the future.

Collectives™ on Stack Overflow

Oracle SQL query to fetch data from log table

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related