Oracle Lag() on date output wrong data

functionsoracleoracle-sql-developer

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 with TO_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 ?

Best Answer

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>

Related Solutions

Oracle – Automate Export/Unload of Data

There are many ways to export data from Oracle and automate the functionality. Be sure to understand exactly what the data export is being used for, though. If it is for interop between systems, then export in a format your receiving system can understand. If it is for backup purposes, go for the exp/expdp (data pump) method because a database backup needs to store much more than simply data. (Better yet, just use RMAN. But I know many DBAs who also like to do full db exports on a regular basis as well.)

You can use a number of tools to accomplish this, TOAD being the one that springs to mind. It has a powerful data export tool that supports scheduling. There is a free version available, but I am uncertain if it has the scheduling functionality. Worth a try, though.

Alternatively, use the tools already at your disposal: SQL*PLUS, PL/SQL, and cron (or the Windows Scheduler if you run Windows). For a good example of how to write a PL/SQL routine that exports a table to a CSV file, see http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:88212348059 . If you use a procedure, you can schedule it from Oracle's own job scheduler.

You can do similar things purely with SQL*PLUS as well (without getting into writing a procedure), but it isn't quite as flexible, but for what you want, it might be just what you need. SQL*PLUS is also easily scriptable so that you can then call it whenever you want via cron/Windows Scheduler.

SQL*PLUS works really well on its own to create good fixed-width reports, but it is possible to do HTML and CSV as well. XML will probably require a procedure of some sort, but I'm not an export here, so SQL*PLUS may not be perfect here (it will output to HTML, though, so that might be good enough). If exporting to Excel, remember that the current versions use XML as their file format, which makes things easy (in one way) and painful in other ways (like needing to know beforehand how many rows you're going to have in the output file).

Regardless, with a little bit of work and the combination of two or three tools, you should be able to export your data in any format you wish on any schedule you desire.

Hope that helps.

Return output of Oracle query

The reason you are not seeing any results is that the FOR loop is not returning any rows as there is no owner called 'O'. To select all the table for a user who is not the logged in user you could use;

FOR t IN (SELECT  t.table_name, t.owner FROM  all_tables t where owner <> USER)

However you will get a failure almost immediately as MAX(DATE) suggests that every table has a column called DATE. Which is not true.

It looks like you probably want a list of all the tables for a specific list of users (not SYS or SYSTEM or any other 'system' account) with the latest date that a row was added or created or something else involving a date. To do that EVERY table for the schema must have the column you want to check.

DECLARE
my_sql VARCHAR(1000);
BEGIN
    FOR t IN (SELECT  t.table_name, t.owner FROM  all_tables t where owner IN ('SCHEMA1', 'SCHEMA2','SCHEMA3')
    LOOP
        my_sql := 'SELECT MAX(update_date) FROM ' || t.owner || '.' || t.table_name ;
        dbms_output.put_line('executing: ' || my_sql);
        EXECUTE IMMEDIATE my_sql;
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
    dbms_output.put_line('ERROR!! -- '  || SQLCODE || '-- ' || sqlerrm || ' --' );
END;

SCHEMA1 / 2 / 3 are the users you know have the tables you are interested in and it assumes that each table has a'update_date' column.