Friday, March 1, 2019

Grabbing UTC Hour from Oracle

A simple sql script for obtaining gmt hour from Oracle
select sys_extract_utc(systimestamp) as UTC,
extract(hour from sys_extract_utc(systimestamp) ) as UTC_HOUR,
extract(hour from cast(to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') as timestamp) ) as  local_hour
from dual

Wednesday, February 20, 2019

ColdFusion POI:Row and updating

I was working with the POI custom tags from Ben Nadel and noticed that the tag was creating the rows rather than updating. Adding a get and check fixed the issue.
<!--- Try Getting the  row. --->
  <cfset VARIABLES.Row = VARIABLES.SheetTag.Sheet.getRow( JavaCast( "int", (VARIABLES.SheetTag.RowIndex - 1) ) )>

  <cfif ! StructKeyExists(VARIABLES, "Row")>
  <cfset VARIABLES.Row = VARIABLES.SheetTag.Sheet.CreateRow(
   JavaCast( "int", (VARIABLES.SheetTag.RowIndex - 1) )
   ) />
  </cfif>

Friday, February 8, 2019

KornShell: Replace spaces with underscores

A simple ksh that will replace a space with "_"


#!/bin/ksh
find /your/file/directory -type f -name "* *" | \
while read file; do \
newfile="$(echo "$file" | sed 's/ /_/g')"
mv "${file}" "${newfile}"
done

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
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