Monday, November 19, 2018

Generating a series of Previous Month End Dates for a given number of years in Oracle

A very short oracle query for generating a range of previous month end dates for a number of years

  with years as  
  (select extract(year from sysdate) - (level - 1) as year  
   from dual  
  connect by level <=  
    (select extract(year from sysdate) -  
        extract(year from date '2000-12-01')   --range of years sysdate to 2001 
     from dual)  
   order by year desc),  
 months as  
  select rownum as month_num,to_char(date '2000-12-01' + numtoyminterval(level,'month'),'MONTH') as month  
    from dual  
   connect by level <= 12  
 select last_day(to_date(to_char(years.year) || '-' ||   
 to_char(months.month_num) || '-1',  
         'yyyy-mm-dd')) as as_of_date  
  from years, months  
  where last_day(to_date(years.year || '-' || months.month, 'yyyy-mm')) < sysdate  

Thursday, August 30, 2018

Full Year Pivot Using Oracle

A quick example of a full year pivot using Oracle
        trunc(some_date_column, 'MONTH'), 
        'MON YYYY'
      ) as transaction_month 
      trunc(some_date_column, 'YEAR') = '01-jan-2018'
  ) PIVOT (
    count(transaction_month) for transaction_month in (
      'JAN 2018', 'FEB 2018', 'MAR 2018', 
      'APR 2018', 'MAY 2018', 'JUN 2018', 
      'JUL 2018', 'AUG 2018', 'SEP 2018', 
      'OCT 2018', 'NOV 2018', 'DEC 2018'

Thursday, January 11, 2018

Oracle Case statements when testing for NULL

Just a little tidbit when working with ORACLE and case statements.

The first method, where the given column name is passed to successive WHEN statements, WHEN must have a value to work with, so you have to convert the null value into something and test for it.

Method 1: Covert the incoming value first

CASE nvl(some_column,'NULL')
         WHEN 'NULL' THEN some_other_column
         WHEN 'ABC' THEN another_column
         END some_column

Method 2: do the condition checks in the WHEN
    WHEN nval(some_column,'NULL') = 'NULL' THEN some_other_column
     WHEN some_column = 'ABC' THEN another_column
     ELSE some_column
     END  some_column