0

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 :

  1. cast all effdt with TO_DATE() and NLS template
  2. 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 ?

Improve this question
6
  • That's great - but I'm still a bit unclear as to what the result should be? Could you put a small matrix with your desired result into the fiddle below the result also please? That "trick" with INSERT ALL... SELECT 1 FROM dual; - where did that come from? It's really cool - instead of multiple UNION ALLs... I'm +1'ing the question for that alone (I also normally upvote New Contributors who provide a fiddle! :-) )
    – Vérace
    Aug 11, 2021 at 7:35
  • There's a problem with your problem - it appears not be reproducible on Oracle 11 or 18 - maybe uninstall and reinstall your server? Maybe even re-download? What happens on the problem system when you run the code at the bottom of this fiddle?
    – Vérace
    Aug 11, 2021 at 7:43
  • INSERT ALL comes from the Bible : StackOF :) - In fact the result should be as showed in the Fiddle. The first EFFDTEND should be 03-JAN-11 but i get 31-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.
    – Nan0
    Aug 11, 2021 at 8:35
  • Set your default date output format NLS_DATE_FORMAT to a "proper" format like YYYY-MM-DD HH24:MI:SS. I assme then then a light will come to you.
    – Wernfried Domscheit
    Aug 11, 2021 at 21:05
  • For debugging, use the same order in the window as in the final query, i.e. SELECT lag(effdt-1,1,null) over (partition by node order by effdt desc), ... ORDER BY node,effdt desc Then it becomes more visible.
    – Wernfried Domscheit
    Aug 11, 2021 at 21:08

2 Answers 2

Reset to default
0

Looks OK on my 11gXE:

SQL> with test (id, name, effdt, node, parent) as
  2    (select 'SHA', 'DEPT', date '2001-01-01', 'A10200', 'TOP'    from dual union all
  3     select 'SHA', 'DEPT', date '2011-01-04', 'A10200', 'X10200' from dual union all
  4     select 'SHA', 'DEPT', date '2014-01-01', 'A10200', 'TOP'    from dual union all
  5     select 'SHA', 'DEPT', date '2014-01-04', 'A10200', 'TOP'    from dual
  6    )
  7  select id, name, effdt,
  8    lag(effdt - 1, 1, null) over (partition by node order by effdt desc) effdtend,
  9    node, parent
 10  from test
 11  order by node, effdt;

ID  NAME EFFDT     EFFDTEND  NODE   PARENT
--- ---- --------- --------- ------ ------
SHA DEPT 01-JAN-01 03-JAN-11 A10200 TOP
SHA DEPT 04-JAN-11 31-DEC-13 A10200 X10200
SHA DEPT 01-JAN-14 03-JAN-14 A10200 TOP
SHA DEPT 04-JAN-14           A10200 TOP

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL>
Improve this answer
1
  • We go to 19c recently and i don't get that problem with previous version..
    – Nan0
    Aug 11, 2021 at 7:19
0

I really don't know why and how it happened but after a refresh by our ITOps of the standby logical DB, my problem is solved..

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.