Oracle DB 19 - Sql developper 19 - NLS DD-MON-RR
I have a table
ID NAME EFFDT NODE PARENT
SHA DEPT 01-JAN-01 A10200 TOP
SHA DEPT 04-JAN-11 A10200 X10200
SHA DEPT 01-JAN-14 A10200 TOP
SHA DEPT 04-JAN-14 A10200 TOP
I'm using Lag()
function in order to create time period
This is my query :
select id
,name
,effdt
,lag(effdt-1,1,null) over (partition by node order by effdt desc) AS EFFDTEND
,node
,parent
FROM
TABLE
ORDER BY node, effdt
Result :
ID NAME EFFDT EFFDTEND NODE PARENT
SHA DEPT 01-JAN-01 31-DEC-09 A10200 TOP
SHA DEPT 04-JAN-11 31-DEC-12 A10200 X10200
SHA DEPT 01-JAN-14 03-JAN-14 A10200 TOP
SHA DEPT 04-JAN-14 05-JAN-14 A10200 TOP
Look at the first lines, i get 31-DEC-09
in place of 03-JAN-11
and 31-DEC-12
in place of 31-DEC-13
After 01-JAN-14
everthing works fine!
I already tried to :
- cast all
effdt
withTO_DATE()
and NLS template - use interval -1 day in place of
effdt-1
SELECT
TO_DATE('04-JAN-11', 'DD-MON-RR')-1
FROM DUAL;
Returns :
03-JAN-11
Update
As requested here is the Fiddle link : Fiddle
On Fiddle and 18c the error is not reproduced
select * from v$version;
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Any idea ?
INSERT ALL... SELECT 1 FROM dual;
- where did that come from? It's really cool - instead of multipleUNION ALL
s... I'm +1'ing the question for that alone (I also normally upvote New Contributors who provide a fiddle! :-) )INSERT ALL
comes from the Bible : StackOF :) - In fact the result should be as showed in the Fiddle. The firstEFFDTEND
should be03-JAN-11
but i get31-DEC-09
. If i run your script on my env i have the same result as your Fiddle. I can't reinstall the server because i'm part of huge department and this part of work is in charge of another service.NLS_DATE_FORMAT
to a "proper" format likeYYYY-MM-DD HH24:MI:SS
. I assme then then a light will come to you.SELECT lag(effdt-1,1,null) over (partition by node order by effdt desc), ... ORDER BY node,effdt desc
Then it becomes more visible.