Thursday, August 30, 2018

A quick example of a full year pivot using Oracle
select 
  * 
FROM 
  (
    select 
      account_no, 
      to_char(
        trunc(some_date_column, 'MONTH'), 
        'MON YYYY'
      ) as transaction_month 
    from 
      some_table 
    where 
      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
         ELSE
          some_column
         END some_column

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