Home » SQL & PL/SQL » SQL & PL/SQL » MAX Analytical Function on Date (Oracle 10g 10.2.0.1.0, OS: Windows Server 2003)
MAX Analytical Function on Date [message #404170] Wed, 20 May 2009 11:38 Go to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Hello Everyone,

I am getting strange output.(Not sure if it has anuthing to do with Date Conversion or Analytical Function) Here goes:

--DDL & INSERT Staements For Sample Data
 CREATE TABLE TEST_TAB
 (
 SR_NO NUMBER,
 PERIOD_NAME VARCHAR2(10) 
 );

Insert into TEST_TAB
   (SR_NO, PERIOD_NAME)
 Values (1, 'MAR-08');
Insert into TEST_TAB
   (SR_NO, PERIOD_NAME)
 Values (1, 'APR-09');
Insert into TEST_TAB
   (SR_NO, PERIOD_NAME)
 Values (1, 'ADJ-08');
Insert into TEST_TAB
   (SR_NO, PERIOD_NAME)
 Values (1, 'SEP-08');
Insert into TEST_TAB
   (SR_NO, PERIOD_NAME)
 Values (2, 'ADJ-08');
Insert into TEST_TAB
   (SR_NO, PERIOD_NAME)
 Values (2, 'JAN-09');
Insert into TEST_TAB
   (SR_NO, PERIOD_NAME)
 Values (2, 'DEC-08');
Insert into TEST_TAB
   (SR_NO, PERIOD_NAME)
 Values (2, 'NOV-08');
COMMIT;

-- Table Output
SQL> SELECT * FROM TEST_TAB;

     SR_NO PERIOD_NAM
---------- ----------
         1 MAR-08
         1 APR-09
         1 ADJ-08
         1 SEP-08
         2 ADJ-08
         2 JAN-09
         2 DEC-08
         2 NOV-08

--Desired Output
     SR_NO PERIOD_NAME  MAX_PERIOD
---------- -----------  ----------
         1 MAR-08       APR-09
         1 APR-09       APR-09
         1 ADJ-08       APR-09
         1 SEP-08       APR-09
         2 ADJ-08       JAN-09
         2 JAN-09       JAN-09
         2 DEC-08       JAN-09
         2 NOV-08       JAN-09



PERIOD_NAME holds data as 'MON-YY' Format with additional data for adjustments for each year like 'ADJ-08'. I want the maximum PERIOD_NAME with respect to the SR_NO. I tried the following query but it is giving me wrong YEAR in the output.

SQL> SELECT sr_no, period_name,
  2                   TO_CHAR
  3            (MAX (TO_DATE (CASE
  4                              WHEN period_name LIKE 'ADJ%'
  5                                 THEN NULL
  6                              ELSE period_name
  7                           END,
  8                           'MON-RR'
  9                          )
 10                 ) OVER (PARTITION BY sr_no),
 11             'Mon-RR'
 12            ) MAX_PERIOD
 13    FROM test_tab;

     SR_NO PERIOD_NAM MAX_PERIOD
---------- ---------- ---------------
         1 MAR-08     Apr-24
         1 APR-09     Apr-24
         1 ADJ-08     Apr-24
         1 SEP-08     Apr-24
         2 ADJ-08     Jan-24
         2 JAN-09     Jan-24
         2 DEC-08     Jan-24
         2 NOV-08     Jan-24

8 rows selected.
-- Year going to 2424 instead of 2009 or 2008


I have successfully got to this point:

SQL> SELECT sr_no, period_name,
  2         TO_DATE (CASE
  3                     WHEN period_name LIKE 'ADJ%'
  4                        THEN NULL
  5                     ELSE period_name
  6                  END,
  7                  'MON-YY'
  8                 ) period
  9    FROM test_tab;

     SR_NO PERIOD_NAM PERIOD
---------- ---------- ---------
         1 MAR-08     01-MAR-08
         1 APR-09     01-APR-09
         1 ADJ-08
         1 SEP-08     01-SEP-08
         2 ADJ-08
         2 JAN-09     01-JAN-09
         2 DEC-08     01-DEC-08
         2 NOV-08     01-NOV-08

8 rows selected.



Problem occurs when I add the MAX Analytical Function. When I add the Analytical Function i get wrong year (Year 2424). Can anyone guide me where I am going wrong.

Thanks & Regards,
Joice
Re: MAX Analytical Function on Date [message #404177 is a reply to message #404170] Wed, 20 May 2009 11:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Your test case works just fine for me,and gives the desired output.

Do you have extra data in the table that you've not posted?
Do you have some odd NLS Settings?
Re: MAX Analytical Function on Date [message #404179 is a reply to message #404170] Wed, 20 May 2009 11:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> SELECT sr_no, period_name,
  2         TO_CHAR
  3            (MAX (TO_DATE (CASE
  4                             WHEN period_name LIKE 'ADJ%'
  5                               THEN NULL
  6                             ELSE period_name
  7                             END,
  8                          'MON-RR'
  9                          )
 10                 ) OVER (PARTITION BY sr_no),
 11           'Mon-RR'
 12           ) MAX_PERIOD
 13  FROM test_tab;
     SR_NO PERIOD_NAM MAX_PE
---------- ---------- ------
         1 MAR-08     Apr-09
         1 APR-09     Apr-09
         1 ADJ-08     Apr-09
         1 SEP-08     Apr-09
         2 ADJ-08     Jan-09
         2 JAN-09     Jan-09
         2 DEC-08     Jan-09
         2 NOV-08     Jan-09

8 rows selected.

SQL> @v

Version Oracle : 10.2.0.4.0

I think you have to patch...

Regards
Michel
Re: MAX Analytical Function on Date [message #404193 is a reply to message #404177] Wed, 20 May 2009 13:30 Go to previous message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Thanks JRowbottom and Michel for the confirmation. Seems it was a version issue. Query Seems to be working fine in Oracle 11.1.0.6.0 I didn't have any other version back then to check my query.


SQL*Plus: Release 11.1.0.6.0 - Production on Wed May 20 23:48:38 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Enter user-name: SCOTT
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT sr_no, period_name,
  2             TO_CHAR
  3                (MAX (TO_DATE (CASE
  4                                 WHEN period_name LIKE 'ADJ%'
  5                                   THEN NULL
  6                                 ELSE period_name
  7                                 END,
  8                              'MON-RR'
  9                              )
 10                    ) OVER (PARTITION BY sr_no),
 11              'Mon-RR'
 12              ) MAX_PERIOD
 13     FROM test_tab;

     SR_NO PERIOD_NAM MAX_PERIOD
---------- ---------- ---------------
         1 MAR-08     Apr-09
         1 APR-09     Apr-09
         1 ADJ-08     Apr-09
         1 SEP-08     Apr-09
         2 ADJ-08     Jan-09
         2 JAN-09     Jan-09
         2 DEC-08     Jan-09
         2 NOV-08     Jan-09

8 rows selected.



Thanks again.

Regards,
Joice
Previous Topic: how to maintain concurrency during process
Next Topic: How to extract CLOB data into text file?
Goto Forum:
  


Current Time: Sat Dec 10 10:49:44 CST 2016

Total time taken to generate the page: 0.08174 seconds