SQL Cheat Sheet

I've put together this page to give quick links to all the Oracle SQL tricks that I find myself continuing to search for on the net. This is a quick reference guide and will be expanded regularly.

Oracle SQL Cheat Sheet

SQL Introduction and Definitions

INSTR()

Create a Delimited Output in SQL Plus

How to Rename a Column in Oracle SQL

How to Delete a Column from a Table

Use of Variables in SQL Plus or PL/SQL

Format Query Results to Display Currency

Display Seconds Past Midnight

Select Only Top Results in a Query

Use of Wildcards in Oracle SQL

What is NVL and how does it work?

How to Use Decode in Oracle SQL

How to Round Off a Number in Oracle SQL

Debug JDBC Connectivity with Oracle Thin Client

Oracle String Matching Algorithm - CHAR_INSTR

If you have any other tips or suggested entries, let me know!

Nice Bike Tom!

Check out these shots of Tom Cruise's new bike. I hear it has a Ducati engine and was custom built for him, sized to fit.

Standard Mail $.02-$.03 rate reduction proposed by PRC

NOTE: THIS IS ONLY PROPOSED AT THIS POINT - NOT YET APPROVED

This morning I received the following pleasant news update from the DMA regarding a temporary rate reduction proposed on all Standard USPS mail:

The Postal Regulatory Commission May 25 offered interim rate relief for Standard Regular flats and catalog mailers in response to the U.S. Postal Service Governors’ request to reconsider the recommended rates in March.

The PRC’s decision grants a temporary rate reduction of 3 cents for all Standard Mail regular flats and 2 cents for Standard Regular nonprofit flats. The temporary transitional rate relief for Standard Regular flats mailers would end Sept. 29.

click to read full story...

They would not require any software change be made but simply a blanket 3 cents per Standard Mail Flat and 2 cents per Non-Profit Standard Mail Flat to be deducted from the overall postage statement.

Mailers concerned should contact their USPS postal reps and support this PRC recommendation.

How to Round Up in Oracle SQL

Recently I needed to provide a report that had numbers rounded to the nearest thousand. I found the ROUND command but this appeared to be only rounding off decimal points to a specified number of spaces. The syntax for this is:

SELECT ROUND(number, decimals)
FROM table;

If you use 2 for the decimals your result would look like this:

SELECT ROUND(2.123,2) FROM DUAL;
Result: 2.12
SELECT ROUND(2.129,2) FROM DUAL;
Result: 2.13

However, I needed something that would round to the nearest thousandth. So I tried a negative number for the decimals like this and found it did exactly what I wanted:

SELECT ROUND(10234,-3) FROM DUAL;
Result: 10000
SELECT ROUND(10567,-3) FROM DUAL;
Result: 11000

While this is a basic Oracle SQL function, it is not clear that you can do this, hence this post.

USPS Rate Change - What do you think?

May 14th, 2006 will go down in history as the most confusing day in mailers history. Unfortunately many people did not install the new presorting software on development machines and get them fully tested before the big date and tried to frantically switch over on May 14th. The tech support team at Firstlogic was overwhelmed with calls - specifically in their Presort support dept. Complaints regarding the post office rejecting their mail, confusing new requirements and general upset due to the increase in postage as a result of the new rate case. Mailers are experiencing anywhere upwards of $.10 per piece increase - the highest increase in standard mail that I can remember.

I saw a post on the DM News website that the USPS claimed the new rate case implementation was going smooth - however it is quite different on the ground in speaking to mailers and software support technicians.

Per the below excerpt from DM News page, it looks as though the Postal Regulation Committee is considering reverting the Standard Flat Mail rate change due to the "rate shock" occurring:

The Governors of the USPS asked that the rates for this category of mail be reconsidered because the price increases recommended by the PRC may impose an unnecessary degree of “rate shock” on the catalog industry and small businesses particularly. The recommended increase for some catalog mailers is as much as 40 percent, which is more than double what the Postal Service had proposed.

Here is a poll to find out your opinion of the new rate case. Please vote and let me know your comments on this.

Is anyone teaching us Morals anymore?

Back in the day, religion had its place in society of instilling moral values in our youth. With the separation of Church and State and the resulting exclusion of any religious material from the classrooms, a void was unintentionally forged - or should I say a chasm.

In the last few years, we've heard more horrific stories of youth violence and their apparent lack of any moral sense. Look at the Columbine shootings or more recently the Virginia Tech rampage. Cho certainly had no sense of morals. Maybe this was the result of the psychiatric - mind-altering drugs he was on. He certainly would have been schooled in the generation where morals were stripped from our classrooms.

Who is teaching our children morals these days anyway?

There is a organization paving the way towards getting Moral values back into society. The Way to Happiness is an organization that promotes "Moral Values for a Modern World. This is a non-religious moral code that anyone can follow.

They have recently launched a campaign of Public Service Announcements that promote this moral code in terms anyone can understand.

Here is a link to a video where you can watch one of these PSAs promoting one of these precepts, Be Competent.

Why Global Data Integrity is Important

Why Global Data Integrity is Important