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

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

Friday, August 25, 2017

Crystal Reports 2016 User Function Library for i18n Translation.

While endeavoring to expand my knowledge of Crystal Reports Designer, I just finished uploading my experiment in creating a Crystal Reports User Function Library in C#.  I've placed the project in my GitHub account https://github.com/wiggick/CRUFL_I18N



Monday, April 11, 2016

FakeSMTP: A Mail Server Emulator

FakeSMTP is a java (jar) application that you launch on your development environment that can act as an SMTP server (receive mode) for testing email functionality in your applications.  You can open up the emails in FakeSMTP, which will launch your email client for viewing.




Example of a ColdFusion web service for AngularJS using $http and JSONP

A small example of a ColdFusion JSONP Web service using $http, that expands on Ben Nadel's excellent article on Using JSONP With $resource In AngularJS.

Form Submit Code for the AngularJS Controller

$scope.onSubmit = function(){
      $http(
        {
          method: 'JSONP',
          url: 'http://your_site/your_cf_jsonp_service.cfm',
          params: {
            callback: "JSON_CALLBACK",
            data: $scope.formModel
          }
        }
      ).then(
        function successCallBack(response){console.log("returned");console.log(response);},
        function errorCallBack(response){console.log("error"); console.log(response);}
      );
  };

Sample ColdFusion AngularJS JSONP Service

<cfsetting showDebugOutput="No">
<!--- Configure the Access Control header for your environment --->
<cfheader name="Access-Control-Allow-Origin" value="*">

<cfparam name="url.callback" type="string" default="angular.callbacks_0">
<!--- Do processing logic, create objects, etc.
then serialize it
Just going to send back a time stamp for this example --->
<cfset result = Now()>
<cfset response= "#url.callback#(#serializeJson(result)#)">
<cfcontent type="text/javascript; charset=utf-8" variable="#charsetDecode(response,'utf-8')#" />