This discussion is archived
9 Replies Latest reply: Feb 12, 2013 10:38 PM by 969918 RSS

Data seperation Pls help

969918 Newbie
Currently Being Moderated
hi all... i have a query...

i have the following detail with me....

Numbers Key abc column date
12345 Y JVN 7/30/2010 20:45
12345 N YVM 4/30/2011 1:45
12354 Y JVN 6/18/2007 14:37
12345 N YVM 5/13/2008 16:28

i have N no of records like this. I need to write a query to find when the key underwent any change (from Y to N and from N to Y).

This is for a single Numbers column(eg:12345). I have several numbers like this.

Can someone help me with a querry for this???

Thanks in advance

Rajesh
  • 1. Re: Data seperation Pls help
    AlbertoFaenza Expert
    Currently Being Moderated
    Duplicate thread: {message:id=10840695}!!

    Don't repost the same question again and again.

    Regards.
    Al
  • 2. Re: Data seperation Pls help
    Etbin Guru
    Currently Being Moderated
    Maybe (boundary conditions not specified)
    select numbers,key,abc,column_date
      from (select numbers,key,abc,column_date,
                   case when key != lead(key,1) over (partition by numbers order by column_date)
                          or key != lag(key,1) over (partition by numbers order by column_date)
                        then 'key change'
                   end key_change
              from your_table
           )
     where key_change is not null
    Regards

    Etbin

    Edited by: Etbin on 8.2.2013 11:09
    Oops! should look before posting. something I wasn't aware of

    Edited by: Etbin on 8.2.2013 11:12
    Sorry to be sorry ;) You were told to move here in Database General Forum
  • 3. Re: Data seperation Pls help
    Karthick_Arp Guru
    Currently Being Moderated
    Rajesh Chennai wrote:
    hi all... i have a query...

    i have the following detail with me....

    Numbers Key abc column date
    12345 Y JVN 7/30/2010 20:45
    12345 N YVM 4/30/2011 1:45
    12354 Y JVN 6/18/2007 14:37
    12345 N YVM 5/13/2008 16:28

    i have N no of records like this. I need to write a query to find when the key underwent any change (from Y to N and from N to Y).

    This is for a single Numbers column(eg:12345). I have several numbers like this.

    Can someone help me with a querry for this???

    Thanks in advance

    Rajesh
    You can use LAG analytic function and check if the key has changed. Something like this
    SQL> with t
      2  as
      3  (
      4  select 12345 no, 'Y' key, 'JVN' abc, to_date('7/30/2010 20:45', 'mm/dd/yyyy hh24:mi') column_date from dual union all
      5  select 12345, 'N', 'YVM', to_date('4/30/2011 1:45', 'mm/dd/yyyy hh24:mi') from dual union all
      6  select 12354, 'Y', 'JVN', to_date('6/18/2007 14:37', 'mm/dd/yyyy hh24:mi') from dual union all
      7  select 12345, 'N', 'YVM', to_date('5/13/2008 16:28', 'mm/dd/yyyy hh24:mi') from dual
      8  )
      9  select no, key, abc, column_date, decode(key, previous_key, ' ', 'CHANGED') change_status
     10    from (
     11            select t.*, nvl(lag(key) over(partition by no order by column_date), key) previous_key
     12              from t
     13         )
     14 /
     
            NO K ABC COLUMN_DA CHANGE_
    ---------- - --- --------- -------
         12345 N YVM 13-MAY-08
         12345 Y JVN 30-JUL-10 CHANGED
         12345 N YVM 30-APR-11 CHANGED
         12354 Y JVN 18-JUN-07
     
    SQL> 
  • 4. Re: Data seperation Pls help
    969918 Newbie
    Currently Being Moderated
    but as we can find the first record is changed to N from Y... so there must be a "changed" indicator there also ryt?
  • 5. Re: Data seperation Pls help
    969918 Newbie
    Currently Being Moderated
    wel dats okay.... No aplogies pls...;)
  • 6. Re: Data seperation Pls help
    Karthick_Arp Guru
    Currently Being Moderated
    Rajesh Chennai wrote:
    but as we can find the first record is changed to N from Y... so there must be a "changed" indicator there also ryt?
    with t
    as
    (
    select 12345 no, 'Y' key, 'JVN' abc, to_date('7/30/2010 20:45', 'mm/dd/yyyy hh24:mi') column_date from dual union all
    select 12345, 'N', 'YVM', to_date('4/30/2011 1:45', 'mm/dd/yyyy hh24:mi') from dual union all
    select 12354, 'Y', 'JVN', to_date('6/18/2007 14:37', 'mm/dd/yyyy hh24:mi') from dual union all
    select 12345, 'N', 'YVM', to_date('5/13/2008 16:28', 'mm/dd/yyyy hh24:mi') from dual
    )
    select no
         , key
         , abc
         , column_date
         , case when previous_key is null and key = 'N' then 'CHANGED'
                when key != previous_key then 'CHANGED'
                else '' end change_status
      from (
              select t.*, lag(key) over(partition by no order by column_date) previous_key
                from t
           )
     
  • 7. Re: Data seperation Pls help
    EdStevens Guru
    Currently Being Moderated
    Alberto Faenza wrote:
    Duplicate thread: {message:id=10840695}!!

    Don't repost the same question again and again.

    Regards.
    Al
    Alberto,

    Actually, that other thread was posted in the "General" forum, and the OP was directed to re-post in this forum, as it is more appropriate to his question. Yes, we get a lot of people spewing the same question all over the place, but in this case, the OP was doing as requested.
  • 8. Re: Data seperation Pls help
    969918 Newbie
    Currently Being Moderated
    there are 100's of records like this, which makes it complex to include all of thm in a loop....
    any other possibilities???
  • 9. Re: Data seperation Pls help
    jeneesh Guru
    Currently Being Moderated
    WITH clause he has taken is for sample data..Just use your table..
    select no
         , key
         , abc
         , column_date
         , case when previous_key is null and key = 'N' then 'CHANGED'
                when key != previous_key then 'CHANGED'
                else '' end change_status
      from (
              select t.*, lag(key) over(partition by no order by column_date) previous_key
                from your_table t
           );

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points