Coding Tips (JavaScript/CSS/VBA/Win32)

Useful code snippets, tips and some Windows applications

Oracle Tips

Create your own Oracle function
Many financial institutions has a peculiar way of counting years. So, for example,
if the month is November or December, and the year is 2004, for financial institutions that will be year 2005.
Let's write an Oracle function that takes a string consisting of year and month portions as a parameter and returns a fiscal year.
An example of yearmonth string: 200410, 200501.
Here is the function: 

(monthend IN varchar2)
	return number
IS year number(4,0);

begin

  if substr (monthend, 5,2)>10
   then fyear:= substr(monthend, 1,4)+1;
  else
     fyear:= substr(monthend, 1,4);
    end if;

  return (fyear);

end;
That is pretty straightforward. Monthend is our parameter (IN) of varchar2 type. On the next line we are saying that the function returns a number. And the third line declares a variable to be used in the function body. The function body is limited by begin and end delimiters. The if else statements have a peculiar structure: statements must end
with a semi-colon; the if structure ends with end if;

Now, we could also implement the same functionality using the Oracle decode function.

decode ( expression , search , result [, search , result]... [, default] )


More details at http://www.techonthenet.com/oracle/functions/decode.htm .

Here is the statement:

decode(  trunc((substr(monthend, 5,2)-1)/10),
		  1, substr(monthend, 1,4)+1,
		 substr(monthend, 1,4) ) as year

Some explanations:
The expression part extracts the month portion, decreases it by 1, divides the result by 10, truncates the decimal portion, and compares the resulting value to 1. If the result is equal to 1, which means that the month portion is 11 or 12, the decode function will return the year portion plus 1. Otherwise, it will return the defaul value, which is the year portion of the monthend string unchanged.
Personally, I find it hard to read, and prefer to use our own function we created earlier.

Test Oracle functions

 Let's say the name of the above function is Fiscal_year . This is how you test it in the SQL Worksheet: 
select Fiscal_year('200511') from dual;

Find duplicate records from two tables

If you have 2 Oracle tables with the same structure, and a primary key is on two or more columns, and you want to find if they contain duplicate records, use the following statement to find duplicate records: 
	SELECT * FROM TABLE_B
	WHERE (COLUMN_A||COLUMN_B) IN
	      (SELECT COLUMN_A||COLUMN_B
	       FROM TABLE_A);
From Ari Kaplan

Create Table like Another Table

 
	CREATE TABLE TBL2 AS SELECT * FROM TBL1;

The above statement will create a new table with the same colums as tbl1. Data from tbl1 will be copied to tbl2.

Or, to create a table based on individual columns : 

	CREATE TABLE TBL2 AS SELECT COL1, COL2 FROM TBL1;

Again, data will be copied too.

Get a system date (current date) in Oracle

	SELECT SYSDATE FROM DUAL;

Select records for the last month 

SELECT * FROM tbl1 WHERE dateField > SYSDATE - 30