Neatest little trick I have ever seen!
An ardent fan, January 09, 2002 - 5:33 pm UTC
Wow! Tom, that was simply awesome! Kind of beating beast (sorry, Oracle) in its own game.
: )
Cesar Salas, January 09, 2002 - 6:30 pm UTC
you are a genius!!!
1000 Thanks...
VERY nicely done
Jim, January 09, 2002 - 6:59 pm UTC
Tom,
Really liked this one !
You have really prompted me to start thinking more
global with your comments and solutions to the recent
trigger question and this one by highlighting the need
to cater for different languages.
Russian problem
Cesar Salas, January 16, 2002 - 7:34 pm UTC
Hi tom,
I have a little problem with RUSSIAN language.
Example.
create or replace view Week
as
select
sysdate Today,
next_day(sysdate,
to_char(to_date('01012001', 'ddmmyyyy'), 'day')) mon,
next_day(sysdate,
to_char(to_date('02012001', 'ddmmyyyy'), 'day')) tue,
next_day(sysdate,
to_char(to_date('03012001', 'ddmmyyyy'), 'day')) wed,
next_day(sysdate,
to_char(to_date('04012001', 'ddmmyyyy'), 'day')) thu,
next_day(sysdate,
to_char(to_date('05012001', 'ddmmyyyy'), 'day')) fri,
next_day(sysdate,
to_char(to_date('06012001', 'ddmmyyyy'), 'day')) sat,
next_day(sysdate,
to_char(to_date('07012001', 'ddmmyyyy'), 'day')) sun
from dual
/
* And then...
select * from week
/
TODAY MON TUE WED THU FRI SAT SUN
--------- --------- --------- --------- --------- --------- --------- ---------
16-JAN-02 21-JAN-02 22-JAN-02 23-JAN-02 17-JAN-02 18-JAN-02 19-JAN-02 20-JAN-02
Works fine, but...
alter session set nls_language = russian
/
select * from week
/
TODAY MON TUE WED THU FRI SAT SUN
--------- --------- --------- --------- --------- --------- --------- ---------
16-¿¿¿-02 20-¿¿¿-02 22-¿¿¿-02 23-¿¿¿-02 22-¿¿¿-02 22-¿¿¿-02 22-¿¿¿-02 20-¿¿¿-02
last give me wrong information...
Thanks!
January 17, 2002 - 7:46 am UTC
Yes, well, do you have russian language supported installed? Are the required NLS files for russian installed on your machine -- did you install them when you installed Oracle?
Next_day question
Steve, April 23, 2003 - 2:41 pm UTC
Hi Tom,
I use Next_day(dd, 'SAT') to get the coming saturday.
but I want to return the date if it is already Saturday?
e.g.
Next_day('4-apr-2003, 'SAT') = 4/5/2003
Next_day('5-apr-2003, 'SAT') = 4/5/2003
Thanks!
Steve
April 23, 2003 - 7:53 pm UTC
just subtract 1 from the date
select next_day( DATE-1, 'SAT' )
that way -- on sat, the input value will be friday and you'll get that saturday back again...
A reader, May 11, 2003 - 6:29 am UTC
I'm facing a strange effect with the following queries:
When I issue
select trunc(DateVal, 'YYYY') + RowNo WeekDate
from (select to_date('01.01.2003', 'dd.mm.yyyy') + RowNo DateVal, RowNo from table(GetNumTab))
where to_char(trunc(DateVal, 'YYYY') + RowNo, 'DY') not in ('SA', 'SO')
and trunc(trunc(DateVal, 'YYYY') + RowNo, 'YYYY') = trunc(DateVal, 'YYYY')
I get the ORA-exceptions 01846, 01841 and 01002 after a while (one minute or so, a few hundred records fetched).
if I remove condition "trunc(trunc(DateVal, 'YYYY') + RowNo, 'YYYY') = trunc(DateVal, 'YYYY')" from the where clause the error disappears. GetNumTab is a pipelined table function that simply returns 0, 1, 2, ...:
function GetNumTab return t_tabNum pipelined
is
i number := 0;
v_tabNum t_tabNum := t_tabNum(t_objNum(i));
begin
loop
pipe row(v_tabNum(v_tabNum.LAST));
v_tabNum.Extend;
i := i +1;
v_tabNum(v_tabNum.LAST) := t_objNum(i);
end loop;
end GetNumTab;
what 's going on, what 's wrong here?
May 11, 2003 - 10:00 am UTC
I would suspect that under the covers you are running out of RAM as you are in a infinite loop allocating a collection to be larger and larger on each iteration.
Look at that code -- tell me, what is the point? what is the goal here? That function plus the query on that function would run forever with no end on a machine with infinite ram.
My guess, you don't have infinite ram.
Not knowing what t_objNum(i) is makes it sort of hard as well -- incomplete examples are not excessively useful.
A reader, May 11, 2003 - 6:10 pm UTC
this is t_objNum's definition:
create or replace type t_objnum as object(RowNo integer);
the function's purpose is just to produce numbers. although this could be achived without extending the collection I decided to add new entries (so the collection grows). but isn't it "funny" that I only run into problems, if I restrict the numbers of executions with that above mentioned clause? ressource consumption should be finite in that case?
May 11, 2003 - 7:05 pm UTC
I could have guessed its structure, it is its content that would be interesting.
It is not "funny", you have an infinite loop. You have a query that never STOPS. What did you expect? You increase the amount of ram you gobble up with every iteration. All that means is on your machine it takes a couple of minutes to gobble up all memory.
I don't know what you mean by if I restrict the number of executions" -- you have NOTHING in your example that would ever restrict the number of executions -- you have a classic infinite loop with no way out of it -- the query will never break out (no "and rownum <= N" in it), the loop will never end.
Nothing surprising here. infinite loops with no way out....
A reader, May 12, 2003 - 10:55 am UTC
you are absolutely RIGHT, it's my fault: "trunc(trunc(DateVal, 'YYYY') + RowNo, 'YYYY') = trunc(DateVal, 'YYYY')" does NOT reduce the number of rows returned from the INLINE VIEW at all.
but nevertheless its EXISTENCE (which in fact does affect the number of rows returned by the outermost query) seems to cause the error. I would expect this to be true in the other scenario (as well). why doesn't the query without "trunc(trunc(DateVal, 'YYYY') + RowNo, 'YYYY') = trunc(DateVal, 'YYYY')" run into the same problem?
thank you very much for your patience.
May 12, 2003 - 1:11 pm UTC
are you overflowing the date in your infinite loop (before running out of ram)
1 select trunc(DateVal, 'YYYY') + RowNo WeekDate
2 from (select to_date('01.01.2003', 'dd.mm.yyyy') + RowNo DateVal, RowNo
3 from <b> (select 9999999999 rowno from dual )</b>
4 )
5 where to_char(trunc(DateVal, 'YYYY') + RowNo, 'DY') not in ('SA', 'SO')
6* and trunc(trunc(DateVal, 'YYYY') + RowNo, 'YYYY') = trunc(DateVal, 'YYYY')
ops$tkyte@ORA920> /
and trunc(trunc(DateVal, 'YYYY') + RowNo, 'YYYY') = trunc(DateVal, 'YYYY')
*
ERROR at line 6:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
I just used dual on line 3 to show you but one of the inifinite values you are returning that would overflow the valid range of dates.
A reader, May 12, 2003 - 3:41 pm UTC
very good point: that 's one of the errors I got.
I'd better stop asking now why/how the presence of that "special" condition in the where clause of the outermost query causes its execution to fail. I've to assume that this additional condition forces ORACLE to loop until the overflow occurs to generate the whole base set. in the absence of that condition it seems that ORACLE does NOT try get ALL rows from the inline view first (in order to apply the predicates to them afterwards).
would you agree? is this a valid conclusion?
thank you for all your help!
May 12, 2003 - 3:50 pm UTC
there is nothing magic or surprising about it.
You have a routine that generates an infinite set of numbers.
These numbers get really big.
When you added that really big number to a date -- you overflowed the date, made it invalid --> bamm, error.
The entire base set is fortunately never generated. Lets say we generated 100 BILLION per second, it would take
ops$tkyte@ORA920> select (rpad('9',38,'9')/ (1000000000*100))/60/60/24/366 from dual;
(RPAD('9',38,'9')/(1000000000*100))/60/60/24/366
------------------------------------------------
3.1623E+19
that many YEARS to generate the inline view.
I do not agree.
The only thing we agree on is this:
a) you have an infinite loop
b) this is totally inappropriate
c) it is a bug in your code
but beyond that -- I don't seem to be translating very well here. Everything I've seen so far is
a) predicable (add a big number to a date, get an error)
b) infinite loops with no possible exit conditions -> bug
A reader, May 14, 2003 - 2:09 pm UTC
... and so I'll never know WHAT prevents ORACLE from trying to reach infinity in case I omit that additional condition ...
I'll now fix the bug.
but please let me ask two last questions related to table functions:
1st: is the order of rows returned predictable (when no order by clause is specified)?
2nd: there seems to be no need to let the internal collection GROW. in my case it works fine to NOT extend that collection but "reuse" the one and only entry over and over again to pipe its content out. can one rely on such behaviour?
thanks for all your help.
if i use it in select it works but not when i use function call
P, February 01, 2005 - 4:59 pm UTC
why is this? does this means its calling different functions under the hood?
16:55:15 SQL> ed
Wrote file afiedt.buf
1 declare
2 x varchar2(20);
3 begin
4 x:=to_char(next_day(sysdate,1),'YYYYMMDD');
5 dbms_output.put_line(x);
6* end;
16:55:32 SQL> /
declare
*
ERROR at line 1:
ORA-01846: not a valid day of the week
ORA-06512: at line 4
16:55:34 SQL> ed
Wrote file afiedt.buf
1 declare
2 x varchar2(20);
3 begin
4 select to_char(next_day(sysdate,1),'YYYYMMDD') into x from dual;
5 dbms_output.put_line(x);
6* end;
16:55:58 SQL> /
20050206
PL/SQL procedure successfully completed.
16:55:59 SQL>
February 02, 2005 - 4:47 am UTC
please log that one with support - plsql got it right, sql got it wrong.
SQL gets it wrong too!
Lise, September 22, 2005 - 3:04 pm UTC
Referring to the Oracle error in the previous review.
If you aren't using sysdate but specify the date as a string using the default format dd-mon-rr the result is just "wrong".
select to_char(next_day('22-sep-05',1),'dd-mon-yyyy') next_day
from dual;
NEXT_DAY
-----------
11-sep-2022
while ....
select to_char(next_day('22-sep-2005',1),'dd-mon-yyyy') next_day
from dual;
ORA-01858: a non-numeric character was found where a numeric was expected
and it works when ...
select to_char(next_day(to_date('22-sep-2005','dd-mon-yyyy'),1),'dd-mon-yyyy') next_day
from dual;
NEXT_DAY
-----------
25-sep-2005
Don't functions normally accept the default date format?
September 22, 2005 - 6:04 pm UTC
I have the default date format defaulting and
ops$tkyte@ORA10GR1> select to_char(next_day('22-sep-05',1),'dd-mon-yyyy') next_day
2 from dual;
NEXT_DAY
-----------
25-sep-2005
ops$tkyte@ORA10GR1> select to_char(next_day('22-sep-2005',1),'dd-mon-yyyy') next_day
2 from dual;
NEXT_DAY
-----------
25-sep-2005
select to_char(next_day(to_date('22-sep-2005','dd-mon-yyyy'),1),'dd-mon-yyyy')
next_day
from dual;
so I'm not sure your default is?
mixed up
tolu ade, January 09, 2006 - 7:50 am UTC
i couldnt understand it at all.
it was really a mix up. pls explain. u can email me regularly
h
tolu ade, January 09, 2006 - 8:11 am UTC
if i can choose another rating, i would have choose wonderful or exellent. tx
Strange Date problem
Bala, April 15, 2006 - 7:14 am UTC
Hi Tom,
I am facing one problem in date format. In a table, there is a varchar2 column which has arrival date and departure date in the format of YYYYMMDD. I want to take the count of number of records where arrival date = departure date. When I compare the both the dates using to_date('YYYYMMDD'), I am getting ORA-0841. Both the dates are in valid format only. I am using the following query to take count.
select count(*) from (
select TRIM(SUBSTR(rec,INSTR(rec,chr(9),1,7)+1,INSTR(rec,chr(9),1,8) - INSTR(rec,chr(9),1,7)-1)) dep_dt,
TRIM(SUBSTR(rec,INSTR(rec,chr(9),1,9)+1, INSTR(rec,chr(9),1,10) - INSTR(rec,chr(9),1,9)-1)) arr_dt
from od_pnr_output_vw a where run_id=343
AND rec_type = 'PSG'
AND pnr_send='Y') where to_date(arr_dt,'YYYYMMDD')=to_date(dep_dt,'YYYYMMDD')
For the above query, I am getting the error.
But If I add rownum in the subquery, I am getting the result and it is not throwing any error.
select count(*) from (
select rownum,TRIM(SUBSTR(rec,INSTR(rec,chr(9),1,7)+1,INSTR(rec,chr(9),1,8) - INSTR(rec,chr(9),1,7)-1)) dep_dt,
TRIM(SUBSTR(rec,INSTR(rec,chr(9),1,9)+1, INSTR(rec,chr(9),1,10) - INSTR(rec,chr(9),1,9)-1)) arr_dt
from od_pnr_output_vw a where run_id=343
AND rec_type = 'PSG'
AND pnr_send='Y') where to_date(arr_dt,'YYYYMMDD')=to_date(dep_dt,'YYYYMMDD')
It is really a strange problem. Please explain to me what is the cause for this strange problem.
April 15, 2006 - 1:14 pm UTC
... In a table, there is a varchar2
column which has arrival date and departure date in the format of YYYYMMDD. ...
No, I beg to differ, you have a varchar2 column. Period. It has some characters in it. Please don't pretend it is a "date", it is just a string.
...
When I compare the both the dates using to_date('YYYYMMDD'), I am getting
ORA-0841.
......
and that is precisely why you cannot pretend it is a date - YOU HAVE BAD DATA IN THERE, data that is NOT A DATE. That is the only thing I can promise you will happen when you try to use a string or a number to store a date. You will just have total GARBAGE, every. single. time. without fail.
You give no example - but this is NOT A REALLY STRANGE PROBLEM, this is an expected side effect of trying to store things in the wrong type - plain and simple.
You are making the assumption that SQL is procedurally language and that things happen in some prescribe order - they do not. They happen in any old order we feel like. If we choose to
a) convert the data
b) then apply the where clause
or
a) apply the where clause
b) then convert the data that passes a)
we can - and that is what you are observing. Your "fix" is not really a fix, you still have the problem - you just aren't hitting it with your test data.
You have given insufficient data to state a "solution" but.... take a look at your query - isn't it obvious that:
where arr_dt = dep_dt
is what you really meant to code? that is, you just want to compare arr_dt and dept_dt in this case don't you - that they are really dates hiding in strings is quite simply "not relevant"
This is just the first time you'll have a problem with this really bad implementation - the optimizer isn't going to like this attribute either. Thing about it - how many days are between December 31, 2005 and January 1, 2006? One. Now, how many strings between 20051231 and 20060101 - lots and lots, bummer for us.
Precedence: where-clause, then select?
David Weigel, April 15, 2006 - 11:41 pm UTC
Following on something in that last reply:
"a) convert the data
b) then apply the where clause
or
a) apply the where clause
b) then convert the data that passes a)"
Can we count on the things in the where-clause being done before logic in the select? Recently I had an update that failed (sometimes) in SQL Server, but it hadn't yet failed in Oracle. It resembled this:
update a
set b =
(select e.c/e.d
from e
where a.whatever = e.whatever
and e.d != 0);
I was getting "divide by zero" errors. In SQL Server, it looks like the "e.c/e.d" could be processed before the "e.d != 0". Might this also happen in Oracle, or have I just been lucky?
April 16, 2006 - 7:29 am UTC
No, you cannot. It can definitely fail in Oracle as well.
select case when e.d != 0 then e.c/e.d end
from e
where a.whatever = e.whatever
and e.d != 0
would be "safe"
Retrieve Last Record
Ramesh Arakkal, April 16, 2006 - 2:18 am UTC
Hi,
In Sybase DB. I would like to retrieve the last record of a given empId.
Table Structure being.
EmpID int,
CheckinDate Date,
CheckinTime Time
Etc
Primary key (EmpID, CheckinDate, CheckinTime).
Records
--------
EmpID CheckinDate CheckinTime
100 12/04/2006 09:00 AM
100 12/04/2006 01:00 PM
101 12/04/2006 10:00 AM
101 13/04/2006 11:00 AM
for 100 : Retrieved data should be 12/04/2006 01:00 PM
for 101 : Retrieved data should be 13/04/2006 11:00 AM.
April 16, 2006 - 7:33 am UTC
In Sybase?
Ok,
step 1: install Oracle
step 2: configure the ODBC hetergenous services
step 3: create a database link from Oracle to Sybase over ODBC
step 4: query
select *
from (select a.*, row_number()
over (partition by empid
order by checkinDate DESC, checkinTime DESC) rn
frmo emp@sybase A)
where rn = 1;
RE : Strange problem
Bala, April 16, 2006 - 6:02 am UTC
Hi Tom,
Thanks for your reply.
First of all, it is not bad data. We have a application, for that application, we have to provide data in certain format like , flt no , arr date, dep date, etc. It is a fixed format. So there is no way of bad data. Another thing is , if I convert the data into date format, I am not getting this error. Only When I compare the two dates, I am getting this error.
You can try by using the following data.
CREATE TABLE TEMP_TEST
( "RUN_ID" NUMBER(8,0) NOT NULL ENABLE,
"PNR_ID" NUMBER(12,0) NOT NULL ENABLE,
"REC" VARCHAR2(1000)
)
/
insert into TEMP_TEST (RUN_ID, PNR_ID, REC)
values (343, 87036725, 'PSG' || chr(9) || '01' || chr(9) || 'EK' || chr(9) || '0530' || chr(9) || 'DXB' || chr(9) || 'COK' || chr(9) || 'K' || chr(9) || '20050523' || chr(9) || '0335' || chr(9) || '20050523' || chr(9) || '0905' || chr(9) || '20050522' || chr(9) || '2335' || chr(9) || '20050523' || chr(9) || '0335' || chr(9) || 'PC' || chr(9) || '?' || chr(9) || '?' || chr(9) || '0' || chr(9) || '?')
/
insert into TEMP_TEST (RUN_ID, PNR_ID, REC)
values (343, 87036725, 'PSG' || chr(9) || '01' || chr(9) || 'EK' || chr(9) || '0074' || chr(9) || 'CDG' || chr(9) || 'DXB' || chr(9) || 'K' || chr(9) || '20050522' || chr(9) || '1530' || chr(9) || '20050523' || chr(9) || '0010' || chr(9) || '20050522' || chr(9) || '1330' || chr(9) || '20050522' || chr(9) || '2010' || chr(9) || 'SN' || chr(9) || '?' || chr(9) || '?' || chr(9) || '0' || chr(9) || '?')
/
insert into TEMP_TEST (RUN_ID, PNR_ID, REC)
values (343, 87036725, 'PSG' || chr(9) || '01' || chr(9) || 'EK' || chr(9) || '0531' || chr(9) || 'COK' || chr(9) || 'DXB' || chr(9) || 'K' || chr(9) || '20050508' || chr(9) || '1030' || chr(9) || '20050508' || chr(9) || '1245' || chr(9) || '20050508' || chr(9) || '0500' || chr(9) || '20050508' || chr(9) || '0845' || chr(9) || 'KL' || chr(9) || '?' || chr(9) || '?' || chr(9) || '0' || chr(9) || '?')
/
insert into TEMP_TEST (RUN_ID, PNR_ID, REC)
values (343, 87036725, 'PSG' || chr(9) || '01' || chr(9) || 'EK' || chr(9) || '0073' || chr(9) || 'DXB' || chr(9) || 'CDG' || chr(9) || 'K' || chr(9) || '20050509' || chr(9) || '0800' || chr(9) || '20050509' || chr(9) || '1310' || chr(9) || '20050509' || chr(9) || '0400' || chr(9) || '20050509' || chr(9) || '1110' || chr(9) || 'SN' || chr(9) || '?' || chr(9) || '?' || chr(9) || '0' || chr(9) || '?')
/
insert into TEMP_TEST (RUN_ID, PNR_ID, REC)
values (343, 87036725, 'PSG' || chr(9) || '01' || chr(9) || 'AF' || chr(9) || '0417' || chr(9) || 'EZE' || chr(9) || 'CDG' || chr(9) || 'H' || chr(9) || '20050521' || chr(9) || '1730' || chr(9) || '20050522' || chr(9) || '1130' || chr(9) || '?' || chr(9) || '?' || chr(9) || '?' || chr(9) || '?' || chr(9) || 'HL' || chr(9) || '?' || chr(9) || '?' || chr(9) || '0' || chr(9) || '?')
/
insert into TEMP_TEST (RUN_ID, PNR_ID, REC)
values (343, 87036725, 'PSG' || chr(9) || '01' || chr(9) || 'EK' || chr(9) || '0526' || chr(9) || 'DXB' || chr(9) || 'HYD' || chr(9) || 'K' || chr(9) || '20050523' || chr(9) || '0345' || chr(9) || '20050523' || chr(9) || '0855' || chr(9) || '20050522' || chr(9) || '2345' || chr(9) || '20050523' || chr(9) || '0325' || chr(9) || 'SN' || chr(9) || '?' || chr(9) || '?' || chr(9) || '0' || chr(9) || '?')
/
commit
/
I have given here the problem data only. I am getting the error only for this particular record using the following query. I have given you that data only in a temporary table. Another strange thing is after I have inserted these particular record into another table and queried that table using the same query, I am not getting this error.
select * from(
select TRIM(SUBSTR(rec,INSTR(rec,chr(9),1,7)+1,INSTR(rec,chr(9),1,8) -
INSTR(rec,chr(9),1,7)-1)) dep_dt,
TRIM(SUBSTR(rec,INSTR(rec,chr(9),1,9)+1, INSTR(rec,chr(9),1,10) -
INSTR(rec,chr(9),1,9)-1)) arr_dt
from od_pnr_output_vw a where run_id=343 and pnr_id=87036725
) where to_date(arr_dt,'YYYYMMDD')=to_date(dep_dt,'YYYYMMDD')
You can use the above same query by just changing the table name as temp_test.
April 16, 2006 - 7:40 am UTC
dude, if there were no bad data - there would be NO ORA-1841 (I know you said 841, but I know you meant 1841 - I know that number like I know 1555, 3113, 600, 7445...)
[tkyte@dellpe ~]$ oerr ora 1841
01841, 00000, "(full) year must be between -4713 and +9999, and not be 0"
// *Cause: Illegal year entered
// *Action: Input year in the specified range
and once I fix your query to query temp_test - no, I do not see the issue:
ops$tkyte@ORA10GR2> select * from(
2 select TRIM(SUBSTR(rec,INSTR(rec,chr(9),1,7)+1,INSTR(rec,chr(9),1,8) - INSTR(rec,chr(9),1,7)-1)) dep_dt,
3 TRIM(SUBSTR(rec,INSTR(rec,chr(9),1,9)+1, INSTR(rec,chr(9),1,10) - INSTR(rec,chr(9),1,9)-1)) arr_dt
4 from temp_test a where run_id=343 and pnr_id=87036725
5 ) where to_date(arr_dt,'YYYYMMDD')=to_date(dep_dt,'YYYYMMDD')
6 /
DEP_DT
-------------------------------------------------------------------------------
ARR_DT
-------------------------------------------------------------------------------
20050523
20050523
20050508
20050508
20050509
20050509
20050523
20050523
Retrieve Last Record
Ramesh Arakkal, April 18, 2006 - 2:26 am UTC
Hi Tom, Thanks for the reply.
Regarding installation of ORACLE management needs to take a deceision, which might take time. In the mean time to solve the problem, if you could provide me pure SQL solution that will be great of you.
"Question was as follows"
In Sybase DB. I would like to retrieve the last record of a given empId.
Table Structure being.
EmpID int,
CheckinDate Date,
CheckinTime Time
Etc
Primary key (EmpID, CheckinDate, CheckinTime).
Records
--------
EmpID CheckinDate CheckinTime
100 12/04/2006 09:00 AM
100 12/04/2006 01:00 PM
101 12/04/2006 10:00 AM
101 13/04/2006 11:00 AM
for 100 : Retrieved data should be 12/04/2006 01:00 PM
for 101 : Retrieved data should be 13/04/2006 11:00 AM.
April 18, 2006 - 9:34 am UTC
time to starting looking for askSOMEONE.sybase.com ???????
really. think about it.
there are obvious answers using ANSI SQL, but since I know why anyone would store the date in one field and time in another - I'm not going to demonstrate (we need the date time in one field so we can:
select * from t
where (empid, DATE_TIME) in (select empid, max(DATE_TIME) from t group by empid)
put the date/time together in something "sortable" so you can find the max date time and you are good to go.
next day of the max date having dd.mm.yyyy increses
saila, April 25, 2011 - 5:11 am UTC
Hi,
I Have a table ,I want to select max date(DATE) and increse one by one(dd.mm.yyyy all field should incress).can anyone help me to get it using insert query?
Thanks for your support.
April 25, 2011 - 11:48 am UTC
insert into table (...)
select max(date)+1, ..... from table;
Note that this is going to be a really bad idea in a multi-user environment of course, think about what would happen if two people run that at about the same time.
Help about next_day function
Vladimir Stanjevic, June 05, 2011 - 9:04 am UTC
Hi Tom,
I have an exam tomorrow, and I have a problem with next_day function
select nk.naziv, next_day((add_month(last_day(r.datum)+1)-1)-1, 'Friday')
from racuni r, nocni_klubovi nk
where r.nkl_id = nk.id
ORA-00904: "ADD_MONTH": invalid identifier
Please help, I don't know where is the problem.
June 06, 2011 - 9:34 am UTC
add_months
it is plural
ops$tkyte%ORA11GR2> select add_months( sysdate, 2 ) from dual;
ADD_MONTH
---------
06-AUG-11
find days
dennisnokia, November 02, 2015 - 1:58 pm UTC
hi how should find a days beteeen departure date and return
TRIP_ID DEPARTURE_DATE RETURN_DATE REGISTRATION EMPLOYEE_NO
-------------------- -------------- ----------- -------------------- --------------------
72941 02-JAN-12 04-JAN-12 BD08AOG 0049547
72942 02-JAN-12 06-JAN-12 BD60BVF 0115692
72943 02-JAN-12 06-JAN-12 BR58BXE 0028539
72944 02-JAN-12 06-JAN-12 BR58BXJ 0003483
72945 02-JAN-12 04-JAN-12 KN10WDG 0018243
72946 02-JAN-12 03-JAN-12 PY06 BYP 0117094
72947 02-JAN-12 04-JAN-12 PY07 DFZ 0065101
72948 02-JAN-12 08-JAN-12 PY11 XJP 0032946
72949 02-JAN-12 07-JAN-12 PY56 BZR 0000350
72950 02-JAN-12 08-JAN-12 PY56 BZS 0067779
72951 02-JAN-12 03-JAN-12 PY58 UHB 0059411
72952 03-JAN-12 11-JAN-12 BD08AOF 0080919
72953 03-JAN-12 05-JAN-12 BR58BXA 0088611
72954 03-JAN-12 09-JAN-12 PW08 LDA 0011614
72955 03-JAN-12 06-JAN-12 PY09 XRG 0019383
72956 03-JAN-12 06-JAN-12 PY56 BZU 0019623
72957 03-JAN-12 07-JAN-12 PY57 XZG 0009037
72958 03-JAN-12 07-JAN-12 PY57 XZH 0103275
72959 03-JAN-12 08-JAN-12 PY59 OSU 0032481
72960 03-JAN-12 06-JAN-12 PY59 OSW 0059411
72961 04-JAN-12 05-JAN-12 BR58BXM 0052861
72962 04-JAN-12 09-JAN-12 BR58BXV 0054864
72963 04-JAN-12 08-JAN-12 P525 CAO 0040079
72964 04-JAN-12 06-JAN-12 PY10 OPM 0117094
72965 04-JAN-12 05-JAN-12 PY12 ZYB 0071609
72966 04-JAN-12 11-JAN-12 PY55 CGO 0041353
72967 04-JAN-12 09-JAN-12 PY58 OGK 0007565
72968 04-JAN-12 06-JAN-12 SDU 567M 0083413
72969 05-JAN-12 07-JAN-12 BD09FNE 0122085
72970 05-JAN-12 14-JAN-12 BD10AYC 0036989
72971 05-JAN-12 09-JAN-12 BR57BXF 0046793
72972 05-JAN-12 07-JAN-12 BR58BXG 0065101
72973 05-JAN-12 10-JAN-12 MR58UMH 0035819
72974 05-JAN-12 08-JAN-12 PW08 EKJ 0081817
72975 05-JAN-12 07-JAN-12 PW59 OSR 0059411
72976 05-JAN-12 10-JAN-12 PY06 BYP 0044597
72977 05-JAN-12 08-JAN-12 PY10 OPL 0037759
72978 05-JAN-12 07-JAN-12 PY12 RSU 0064734
72979 06-JAN-12 15-JAN-12 BR58BXC 0013322
72980 06-JAN-12 08-JAN-12 PY11 OAA 0052861
72981 06-JAN-12 08-JAN-12 PY57 XZK 0071609
72982 07-JAN-12 11-JAN-12 KN10WDG 0028539
72983 08-JAN-12 11-JAN-12 4585 AW 0000350
72984 08-JAN-12 11-JAN-12 PY07 DFZ 0059009
72985 08-JAN-12 12-JAN-12 PY09 XRH 0059411
72986 08-JAN-12 14-JAN-12 PY10 OPM 0049743
72987 08-JAN-12 13-JAN-12 PY12 RSV 0042735
72988 08-JAN-12 14-JAN-12 PY12 ZYA 0003483
72989 08-JAN-12 14-JAN-12 PY60 XPM 0115692
72990 08-JAN-12 10-JAN-12 PY61 RNU 0083413
72991 09-JAN-12 11-JAN-12 BD08AOG 0032946
72992 09-JAN-12 12-JAN-12 BD09FNE 0103275
72993 09-JAN-12 11-JAN-12 BD60BVF 0072181
72994 09-JAN-12 10-JAN-12 BR58BXA 0016462
72995 09-JAN-12 10-JAN-12 PY09 XRG 0065101
72996 09-JAN-12 13-JAN-12 PY56 BZU 0081817
72997 09-JAN-12 11-JAN-12 PY58 UHB 0096114
72998 09-JAN-12 12-JAN-12 PY61 RNV 0064734
72999 09-JAN-12 10-JAN-12 WY51OLV 0071609
73000 10-JAN-12 11-JAN-12 BR58BXE 0041668
73001 10-JAN-12 17-JAN-12 BR58BXG 0112122
73002 10-JAN-12 12-JAN-12 PW09 EKX 0032481
73003 11-JAN-12 13-JAN-12 BD10AYV 0083413
73004 11-JAN-12 16-JAN-12 BR58BXV 0016462
73005 11-JAN-12 17-JAN-12 P525 CAO 0046793
73006 11-JAN-12 18-JAN-12 PW08 LDA 0018243
73007 11-JAN-12 12-JAN-12 PY57 XZK 0035819
73008 11-JAN-12 15-JAN-12 WY51OLV 0037759
73009 12-JAN-12 13-JAN-12 BD08AOG 0077517
73010 12-JAN-12 16-JAN-12 BR58BXE 0012437
73011 12-JAN-12 21-JAN-12 PY09 XRG 0039136
73012 12-JAN-12 13-JAN-12 PY10 OPN 0044597
73013 12-JAN-12 15-JAN-12 PY56 BZS 0065101
73014 12-JAN-12 18-JAN-12 PY58 UHF 0072181
73015 12-JAN-12 13-JAN-12 PY59 OSU 0032946
73016 13-JAN-12 14-JAN-12 BD08AOC 0032481
73017 13-JAN-12 21-JAN-12 BR58BXJ 0028539
73018 13-JAN-12 14-JAN-12 PY12 ZYB 0064734
73019 13-JAN-12 14-JAN-12 PY56 BZR 0103275
73020 13-JAN-12 15-JAN-12 PY56 BZT 0096114
73021 13-JAN-12 14-JAN-12 PY57 XZG 0059411
73022 13-JAN-12 15-JAN-12 PY58 OGK 0041668
73023 14-JAN-12 15-JAN-12 4585 AW 0007565
73024 14-JAN-12 21-JAN-12 BD08AOF 0059411
73025 14-JAN-12 16-JAN-12 BD09FNE 0077517
November 03, 2015 - 12:55 am UTC
date1 - date2 will return the days difference, eg
SQL> select sysdate - date '2015-10-10' from dual;
SYSDATE-DATE'2015-10-10'
------------------------
24.3719907