SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Select Query in Want unique time.

718557Apr 23 2010 — edited Apr 28 2010

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.

This post has been answered by Aketi Jyuuzou on Apr 24 2010

Jump to Answer

Aketi Jyuuzou

This is one way :D
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.

1 person found this helpful

Aketi Jyuuzou

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

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

Answer

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

Marked as Answer by 718557 · Sep 27 2020

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.

Boneist

If you're getting that error, then you must be doing something different to the example you've given, such as subtracting two timestamps, etc.

You'd find it much more helpful if you could give a full example of what you're trying to do, rather than showing us a small part and expecting us to work out what you're really trying to do piece by piece.

718557

Here is my full example.

I have one table as per below.

CREATE TABLE T_SESSIONCONT_UHH_EXTERNAL
(
COUNTRY_CODE VARCHAR2(3 BYTE),
COMPANY_CODE VARCHAR2(3 BYTE),
INPUT_SRCE VARCHAR2(1 BYTE),
ROUTE VARCHAR2(5 BYTE),
LOCATION VARCHAR2(6 BYTE),
TCOMM_DATE VARCHAR2(10 BYTE),
TIME VARCHAR2(5 BYTE)
);

I have data as per below.
------------------------
SET DEFINE OFF;
Insert into T_STLMNTTBL_UHH
(COUNTRY_CODE, COMPANY_CODE, INPUT_SRCE, ROUTE, LOCATION, TCOMM_DATE, TIME)
Values
('119', '003', 'H', '90620', '000161', '21/04/2010', '23:10');
Insert into T_STLMNTTBL_UHH
(COUNTRY_CODE, COMPANY_CODE, INPUT_SRCE, ROUTE, LOCATION, TCOMM_DATE, TIME)
Values
('119', '003', 'H', '90620', '000161', '21/04/2010', '23:10');
Insert into T_STLMNTTBL_UHH
(COUNTRY_CODE, COMPANY_CODE, INPUT_SRCE, ROUTE, LOCATION, TCOMM_DATE, TIME)
Values
('119', '003', 'H', '90620', '000161', '21/04/2010', '23:59');
Insert into T_STLMNTTBL_UHH
(COUNTRY_CODE, COMPANY_CODE, INPUT_SRCE, ROUTE, LOCATION, TCOMM_DATE, TIME)
Values
('119', '003', 'H', '90620', '000161', '21/04/2010', '23:59');
Insert into T_STLMNTTBL_UHH
(COUNTRY_CODE, COMPANY_CODE, INPUT_SRCE, ROUTE, LOCATION, TCOMM_DATE, TIME)
Values
('119', '003', 'H', '90620', '000161', '21/04/2010', '10:10');
Insert into T_STLMNTTBL_UHH
(COUNTRY_CODE, COMPANY_CODE, INPUT_SRCE, ROUTE, LOCATION, TCOMM_DATE, TIME)
Values
('119', '003', 'H', '90620', '000161', '21/04/2010', '10:10');
COMMIT;

alter session set nls_date_format='DD-MON-RR';

In my database NLS_DATE_FORMAT set as DD-MON-RR (we have no right's set it as per our requirement).
Above select query use to_date with formate 'yyyymmdd hh24:mi' which not work with my existing NLS_DATE_FORMAT setting.

I want only increment time if it duplicate to avoide primary key vilotion at the time of insert this table data to other table.

Boneist

The issue is probably due to the:

TCOMM_DATE VARCHAR2(10 BYTE),
TIME VARCHAR2(5 BYTE)

Why store the time separately to the date? Why not just store it as one DATE column, given that DATEs also store time?

In order to convert those two columns into a DATE, you'll have to do: to_date(tcomm_date||' '||time, 'dd/mm/yyyy hh24:mi:ss').

Once you've done that, then you can use the query above.

718557

I want say only one question i can not change my NLS_DATE_FORMAT ,
currently it set as DD-MON-RR so i can't access output with hh24:mi:ss.

alter session set nls_date_format='DD-MON-RR';

SQL> select to_date('10/12/2010' ||' '||'23:59', 'dd/mm/yyyy hh24:mi:ss') from dual;

TO_DATE('
---------
10-DEC-10

Pleae try to understand my origional question, I don't want to say above query is wrong
but can it modify in that way i not need to use to_date function or any date releated function.

Boneist

In order to output a date in the format you want, it must first be in date format. THEN you can use to_char to convert it back into a string in the required output!

So for your example:

select to_char(to_date('10/12/2010' ||' '||'23:59', 'dd/mm/yyyy hh24:mi:ss'), 'yyyy-mm-dd hh24:mi') dt
from   dual;

DT              
----------------
2010-12-10 23:59

(use whatever format you require, of course)

1 person found this helpful

Aketi Jyuuzou

And We can use recursive with clause B-)
Hehe I have used PostgreSQL8.4 :8}
Because Oracle11gR2 has issues 1061530

with recursive work(ID,Val) as(
values('00001',timestamp '2010-10-12 10:10:00'),
      ('00001',timestamp '2010-10-12 10:10:00'),
      ('00001',timestamp '2010-10-12 10:10:00'),
      ('00001',timestamp '2010-10-12 10:10:00'),
      ('00002',timestamp '2010-10-12 10:10:00'),
      ('00002',timestamp '2010-10-12 10:10:00')),
tmp(ID,Val,rn) as(
select ID,Val,Row_Number() over(partition by ID order by Val)
  from work),
rec(ID,Val,rn) as(
select ID,Val,rn
  from tmp
 where rn=1
union all
select b.ID,greatest(a.Val+interVal '1' minute,b.Val),b.rn
  from rec a,tmp b
 where a.ID=b.ID
   and a.rn+1=b.rn)
select*from rec order by ID,Val;

  id   |         val         | rn
-------+---------------------+----
 00001 | 2010-10-12 10:10:00 |  1
 00001 | 2010-10-12 10:11:00 |  2
 00001 | 2010-10-12 10:12:00 |  3
 00001 | 2010-10-12 10:13:00 |  4
 00002 | 2010-10-12 10:10:00 |  1
 00002 | 2010-10-12 10:11:00 |  2

1 - 16

Locked Post

New comments cannot be posted to this locked post.

Locked on May 26 2010

Added on Apr 23 2010

16 comments

2,804 views

SQL & PL/SQL

Select Query in Want unique time.

Comments

Post Details