Select Query in Want unique time.

718557

Please go through below detail

I have one table.

File Id, Date, Time

00001 10/12/2010 10:10
00001 10/12/2010 10:10
00001 10/12/2010 10:10
00001 10/12/2010 10:11

00002 10/12/2010 10:10
00002 10/12/2010 10:10

I want unique fileid,date and time in select query only.

If apply group by File Id, Date, Time then I want increment time +1.

New column
----------
00001 10/12/2010 10:10 10:10
00001 10/12/2010 10:10 10:12 <-- We can not set 10:11 due to it's already exist in table.
00001 10/12/2010 10:10 10:13
00001 10/12/2010 10:11 10:11


In short i would like select fileid,date,time,(Id duplicate entry then time +1 to generate uniquness).



-------------------------------------
I have do this for myself but i face this problem.


Query to fatch below all duplicate row

time+1
00001 10/12/2010 10:10 10:10
00001 10/12/2010 10:10 10:11 <-- But this enty again become duplicate due to it's already exist in table.
00001 10/12/2010 10:10 10:12

Thanks in advance.

Aketi Jyuuzou

Hehe there is more simple one :-)

alter session set nls_date_format = 'yyyy/mm/dd hh24:mi';

with t as(
select '00001' id,to_date('20101012 10:10','yyyymmdd hh24:mi') val from dual union all
select '00001',   to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
select '00001',   to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
select '00001',   to_date('20101012 10:11','yyyymmdd hh24:mi') from dual union all
select '00002',   to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
select '00002',   to_date('20101012 10:10','yyyymmdd hh24:mi') from dual)
select ID,Val
  from t
 model
partition by(ID)
dimension by(row_number() over(partition by id order by Val) as rn)
measures(Val)
rules(Val[rn > 1] order by rn
    = greatest(Val[cv()-1]+interVal '1' minute,
               Val[cv()]));

ID     VAL
-----  ----------------
00001  2010/10/12 10:10
00001  2010/10/12 10:11
00001  2010/10/12 10:12
00001  2010/10/12 10:13
00002  2010/10/12 10:10
00002  2010/10/12 10:11

Aketi Jyuuzou

This is one way
But I recommend using TableFunction.

alter session set nls_date_format = 'yyyy/mm/dd hh24:mi';

with t(Val) as(
select to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
select to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
select to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
select to_date('20101012 10:11','yyyymmdd hh24:mi') from dual)
select Val+NumToDsInterVal(-1+Row_Number() over(partition by Val order by 1),'Minute') as Val from(
select Val+NumToDsInterVal(-1+Row_Number() over(partition by Val order by 1),'Minute') as Val from(
select Val+NumToDsInterVal(-1+Row_Number() over(partition by Val order by 1),'Minute') as Val from(
select Val+NumToDsInterVal(-1+Row_Number() over(partition by Val order by 1),'Minute') as Val from(
select Val+NumToDsInterVal(-1+Row_Number() over(partition by Val order by 1),'Minute') as Val from(
select Val+NumToDsInterVal(-1+Row_Number() over(partition by Val order by 1),'Minute') as Val from(
select Val+NumToDsInterVal(-1+Row_Number() over(partition by Val order by 1),'Minute') as Val from(
select Val+NumToDsInterVal(-1+Row_Number() over(partition by Val order by 1),'Minute') as Val from(
select Val+NumToDsInterVal(-1+Row_Number() over(partition by Val order by 1),'Minute') as Val from(
select Val+NumToDsInterVal(-1+Row_Number() over(partition by Val order by 1),'Minute') as Val from(
select Val+NumToDsInterVal(-1+Row_Number() over(partition by Val order by 1),'Minute') as Val from(
select Val+NumToDsInterVal(-1+Row_Number() over(partition by Val order by 1),'Minute') as Val from(
(select Val+NumToDsInterVal(-1+Row_Number() over(partition by Val order by 1),'Minute') as Val from t)
))))))))))));

VAL
----------------
2010/10/12 10:10
2010/10/12 10:11
2010/10/12 10:12
2010/10/12 10:13

Aketi Jyuuzou

I have made TableFunction B-)

create table dayT(ID,Val) as
select 1,to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
select 1,to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
select 1,to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
select 1,to_date('20101012 10:11','yyyymmdd hh24:mi') from dual union all
select 2,to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
select 2,to_date('20101012 10:10','yyyymmdd hh24:mi') from dual;

create or replace type ReturnType as object(
ID  number(1),
Val date);
/

create or replace type ReturnTypeSet as table of ReturnType;
/

create or replace function MinuteInc return ReturnTypeSet PipeLined IS
    hasChange boolean;
    outR ReturnType := ReturnType(NULL,NULL);

    cursor cur is select * from dayT;
    type saveDataDef is table of cur%rowType index by binary_integer;
    saveData saveDataDef;
begin
    open cur;
    fetch cur bulk collect into saveData;
    close cur;

    for I in 1..saveData.Last Loop
        hasChange := false;
        Loop
            for J in 1..saveData.Last Loop
                continue when I = J;
                if saveData(I).ID  = saveData(J).ID
               and saveData(I).Val = saveData(J).Val then
                    saveData(I).Val := saveData(I).Val+ InterVal '1' minute;
                    hasChange := true;
                end if;
            end Loop;
            exit when hasChange=false;
            hasChange := false;
        end Loop;
    end Loop;

    for I in 1..saveData.Last Loop
        outR.ID  := saveData(I).ID;
        outR.Val := saveData(I).Val;
        pipe row(outR);
    end Loop;
end;
/

sho err

alter session set nls_date_format = 'yyyy/mm/dd hh24:mi';

select ID,Val from table(MinuteInc)
order by ID,Val;

ID  Val
--  ----------------
 1  2010/10/12 10:10
 1  2010/10/12 10:11
 1  2010/10/12 10:12
 1  2010/10/12 10:13
 2  2010/10/12 10:10
 2  2010/10/12 10:11

Solomon Yakobson

Aketi Jyuuzou wrote:
I have made TableFunction

Much simpler with MODEL B-) :

with t as(
          select '00001' id,to_date('20101012 10:10','yyyymmdd hh24:mi') val from dual union all
          select '00001',to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
          select '00001',to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
          select '00001',to_date('20101012 10:11','yyyymmdd hh24:mi') from dual union all
          select '00002',to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
          select '00002',to_date('20101012 10:10','yyyymmdd hh24:mi') from dual
         )
select  id,
        val
  from  t
  model
    dimension by(
                 id,
                 row_number() over(partition by id order by val) rn
                )
    measures(
             val
            )
    rules(
          val[any,rn > 1] order by id,rn = greatest(val[cv(id),cv(rn)],max(val)[cv(id),rn < cv(rn)] + interval '1' minute)
         )
/

ID    VAL
----- ----------------
00001 2010/10/12 10:10
00001 2010/10/12 10:11
00001 2010/10/12 10:12
00001 2010/10/12 10:13
00002 2010/10/12 10:10
00002 2010/10/12 10:11

6 rows selected.

SQL> 

SY.

Aketi Jyuuzou

Wow that is very cool solution !!!!!
That can become more simpler with using partition by(ID)

alter session set nls_date_format = 'yyyy/mm/dd hh24:mi';

with t as(
select '00001' id,to_date('20101012 10:10','yyyymmdd hh24:mi') val from dual union all
select '00001',   to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
select '00001',   to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
select '00001',   to_date('20101012 10:11','yyyymmdd hh24:mi') from dual union all
select '00002',   to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
select '00002',   to_date('20101012 10:10','yyyymmdd hh24:mi') from dual)
select ID,val
  from t
 model
partition by(ID)
dimension by(row_number() over(partition by id order by val) rn)
measures(val)
rules(val[rn > 1] order by rn 
    = greatest(val[cv()],
      max(val)[rn < cv()] + interval '1' minute));

ID     val
-----  ----------------
00001  2010/10/12 10:10
00001  2010/10/12 10:11
00001  2010/10/12 10:12
00001  2010/10/12 10:13
00002  2010/10/12 10:10
00002  2010/10/12 10:11

Aketi Jyuuzou

Hehe there is more simple one :-)

alter session set nls_date_format = 'yyyy/mm/dd hh24:mi';

with t as(
select '00001' id,to_date('20101012 10:10','yyyymmdd hh24:mi') val from dual union all
select '00001',   to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
select '00001',   to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
select '00001',   to_date('20101012 10:11','yyyymmdd hh24:mi') from dual union all
select '00002',   to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
select '00002',   to_date('20101012 10:10','yyyymmdd hh24:mi') from dual)
select ID,Val
  from t
 model
partition by(ID)
dimension by(row_number() over(partition by id order by Val) as rn)
measures(Val)
rules(Val[rn > 1] order by rn
    = greatest(Val[cv()-1]+interVal '1' minute,
               Val[cv()]));

ID     VAL
-----  ----------------
00001  2010/10/12 10:10
00001  2010/10/12 10:11
00001  2010/10/12 10:12
00001  2010/10/12 10:13
00002  2010/10/12 10:10
00002  2010/10/12 10:11

718557

Fentastic, yes it's working but a little change,
I want time as it is means 23:11 it should not convert to 11:11:00 PM

Please check below function,I thik it's give me wrong output.

with t as(
select '00001' id,to_date('20101012 23:10','yyyymmdd hh24:mi') val from dual union all
select '00001', to_date('20101012 23:10','yyyymmdd hh24:mi') from dual union all
select '00001', to_date('20101012 23:59','yyyymmdd hh24:mi') from dual union all
select '00001', to_date('20101012 23:59','yyyymmdd hh24:mi') from dual union all
select '00002', to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
select '00002', to_date('20101012 10:10','yyyymmdd hh24:mi') from dual)
select ID,Val
from t
model
partition by(ID)
dimension by(row_number() over(partition by id order by Val) as rn)
measures(Val)
rules(Val[rn > 1] order by rn
= greatest(Val[cv()-1]+interVal '1' minute,
Val[cv()]));


Output
------
ID VAL

00001 10/12/2010 11:10:00 PM
00001 10/12/2010 11:11:00 PM
00001 10/12/2010 11:59:00 PM
00001 10/13/2010
00002 10/12/2010 10:10:00 AM
00002 10/12/2010 10:11:00 AM

But I want output like below.
--------------------------------

00001 10/12/2010 23:10
00001 10/12/2010 23:11
00001 10/12/2010 23:59
00001 10/13/2010 23:00
00002 10/12/2010 10:10
00002 10/12/2010 10:11

Hoek

I want time as it is means 23:11 it should not convert to 11:11:00 PM

That has nothing to do with the query.
Just adjust your NLS_DATE_FORMAT:

SQL> alter session set nls_date_format='dd/mm/yyyy hh:mi:ss PM';

Session altered.

SQL> with t as(
  2  select '00001' id,to_date('20101012 23:10','yyyymmdd hh24:mi') val from dual union all
  3  select '00001', to_date('20101012 23:10','yyyymmdd hh24:mi') from dual union all
  4  select '00001', to_date('20101012 23:59','yyyymmdd hh24:mi') from dual union all
  5  select '00001', to_date('20101012 23:59','yyyymmdd hh24:mi') from dual union all
  6  select '00002', to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
  7  select '00002', to_date('20101012 10:10','yyyymmdd hh24:mi') from dual)
  8  select ID,Val
  9  from t
 10  model
 11  partition by(ID)
 12  dimension by(row_number() over(partition by id order by Val) as rn)
 13  measures(Val)
 14  rules(Val[rn > 1] order by rn
 15  = greatest(Val[cv()-1]+interVal '1' minute,
 16  Val[cv()]));

ID    VAL
----- ----------------------
00001 12/10/2010 11:10:00 PM
00001 12/10/2010 11:11:00 PM
00001 12/10/2010 11:59:00 PM
00001 13/10/2010 12:00:00 AM
00002 12/10/2010 10:10:00 AM
00002 12/10/2010 10:11:00 AM

6 rows selected.

SQL> alter session set nls_date_format='dd/mm/yyyy hh24:mi';

Session altered.

SQL> with t as(
  2  select '00001' id,to_date('20101012 23:10','yyyymmdd hh24:mi') val from dual union all
  3  select '00001', to_date('20101012 23:10','yyyymmdd hh24:mi') from dual union all
  4  select '00001', to_date('20101012 23:59','yyyymmdd hh24:mi') from dual union all
  5  select '00001', to_date('20101012 23:59','yyyymmdd hh24:mi') from dual union all
  6  select '00002', to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
  7  select '00002', to_date('20101012 10:10','yyyymmdd hh24:mi') from dual)
  8  select ID,Val
  9  from t
 10  model
 11  partition by(ID)
 12  dimension by(row_number() over(partition by id order by Val) as rn)
 13  measures(Val)
 14  rules(Val[rn > 1] order by rn
 15  = greatest(Val[cv()-1]+interVal '1' minute,
 16  Val[cv()]));

ID    VAL
----- ----------------
00001 12/10/2010 23:10
00001 12/10/2010 23:11
00001 12/10/2010 23:59
00001 13/10/2010 00:00
00002 12/10/2010 10:10
00002 12/10/2010 10:11

6 rows selected.

718557

Yes you are right with the use of alter session set nls_date_format='dd/mm/yyyy hh24:mi'
I got the result that i want.

But the problem is that ,
My NLS_DATE_FORMAT : DD-MON-RR It's set on client machine
and we have no right's to change it.

I have got date and time from external table in short
I got both date and time in separate varchar2 data type column not a date datatype.

So, is there any other why to achive required output without change NLS_DATE_FORMAT
or without use the date related function in quty.

Edited by: user10594896 on Apr 27, 2010 2:50 AM

Boneist

Use to_char to produce whatever format you need:

with t as(
select '00001' id,to_date('20101012 10:10','yyyymmdd hh24:mi') val from dual union all
select '00001',   to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
select '00001',   to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
select '00001',   to_date('20101012 10:11','yyyymmdd hh24:mi') from dual union all
select '00002',   to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
select '00002',   to_date('20101012 10:10','yyyymmdd hh24:mi') from dual)
select ID, to_char(val, 'yyyy-mm-dd hh24:mi:ss') val
  from t
 model
partition by(ID)
dimension by(row_number() over(partition by id order by val) rn)
measures(val)
rules(val[rn > 1] order by rn 
    = greatest(val[cv()],
      max(val)[rn < cv()] + interval '1' minute));

ID    VAL                
----- -------------------
00001 2010-10-12 10:10:00
00001 2010-10-12 10:11:00
00001 2010-10-12 10:12:00
00001 2010-10-12 10:13:00
00002 2010-10-12 10:10:00
00002 2010-10-12 10:11:00

718557

We can not use to_char it's give below error.
ORA-30081: invalid data type for datetime/interval arithmetic

Due to we use interVal date related function.