How to use Decode in Oracle SQL

The use of the decode function in Oracle SQL is pretty straightforward. You simply specify the column that you need conditional logic on and then specify the conditions as follows:

SELECT DECODE (value,
   <if this value>, <return this value>,
   < if this value>, <return this value>,
   ....)
FROM dual;

If you have a default value that you want to display if any of the conditions are not met, you can add another comma and specify the default value like this:

SELECT DECODE (value,
    if this value, return this value,
    if this value, return this value,
    otherwise this value
FROM dual;

For a thorough description of all features of this DECODE function, see the Puget Sound Oracle User's Group description.

Here are some further examples from another post on my blog:

Most Oracle SQL users do not realize the power of this function. Hopefully this post will enlighten some on it.

What is DPV (Delivery Point Validation)?

Mailers today who do not use a DPV service are either really out of the loop or they don't really care whether their mail actually arrives or not.



I've been in the category of "really out of the loop" until recently when I became aware of the pitfalls of not using a DPV service to ensure your mailing address is deliverable.



Let's say you process your mailing list through a standardization service without DPV and then try to merge all duplicates together in your database. If you do not validate down to DPV level, you may be losing address data and names from your list . DPV validation will tell you that the mail will actually get delivered to a delivery POINT which other validation levels can not do. ZIP4 encoding can only give you the general range of zip codes and tell you whether or not these are valid ranges, but for all you know you may be mailing to an empty lot or a torn down building, not to mention the high rises that won't deliver your mail without that apartment number.



So mailers who are interested in keeping their lists in good shape should choose some form of DPV solution. I am looking into the PostalSoft validation which is described on their website:

Delivery Point Validation (DPV)

By adding delivery point
validation to your data cleansing process, you can bring data
validation to finer precision. A DPV solution:

• Determines whether a particular address — a specific house number,
  apartment number, or suite number — is known to the USPS (United States
  Postal Service) as a valid point for delivering mail, which helps
  reduce mail-order fraud.
• Identifies whether an address is a Commercial Mail Receiving Agency
  (CMRA), such as The UPS Store, which can reduce fraudulent credit card
  orders.
• Increases the accuracy of matched records and helps create a more
  accurate view of each customer, resulting in more sophisticated
  marketing campaigns, such as loyalty programs, or more highly
  personalized offers.

powered by performancing firefox

ZIP Code Maps

Here is a basic ZIP Code map showing the different state ZIP codes and ranges. I've been looking for a good ZIP Code map for a while now and the above is pretty straightforward. While this doesn't give you much in terms of detail, it is a good overview.

Next is a great link to a ZIP code distribution map which is an interactive Java applet where you can see the exact location of each ZIP Code range or down to the specific zip code itself.

zipdecode | ben fry

Source Code

Jigloo for Eclipse?

I've been using Eclipse now for a few months. Originally I started out with the Jigloo form builder tool which I found quite useful. From an earlier post:

Building GUIs for rich client apps in Eclipse is not the easiest thing in the world. I'm looking for windows builders comparable to the Net Beans GUI builder.

I am downloading the Jigloo GUI builder which sounds pretty good but that is coming from company who made it.

What about WindowsBuilderPro? This is a commercial product, not free like Jigloo. How is it better?

I'll try both and see for myself...

On the install, Jigloo is very simple and is just like any other Eclipse plugins. Just copy to the features and plugins directories and your done. WindowBuilder on the other hand has you walk through a install wizard which took only a couple minutes to run through. (Make sure you don't have Eclipse running during the install).

WindowsBuilder has several example apps you can try. In 5 minutes of testing this builder I found it very user friendly. I liked the "quick view" feature where without even compiling you can get a feel for how your app will look during runtime. Now on to Jigloo.

After the few minutes I found it easy to create a basic app. Nothing difficult. I liked the two screen (code/app) view that shows the two-way changes so if you edit the GUI the code changes or you edit the code and the GUI changes.

So far they are both comparable products and I'll spend a bit more time using each on an actual application to see which is the better product.

However, once I found my feet in making Swing applications, I found using a GUI builder to be more of a pain in the neck than a help. I see these GUI builders like training wheels when learning to ride your bike. It is really not that difficult once you get the hang of it, and those training wheels sure get annoying when trying to speed down that big hill...

My Scientology Wedding

I was glad to hear that Tom Cruise picked the same Scientology Wedding ceremony that my wife and I chose when we got married 5 years ago. This was the Double Ring ceremony and during this the minister holds up two rings and asks you to picture the ARC Triangle in the middle as this represents Affinity, Reality and Communication - a vital part of a lasting marraige. A secret to a succesful Scientology marraige is that you have continuously create it. From the Scientology website on weddings:

"When someone begins on that arrangement called marriage, he is getting into something which is, to say the least, adventurous. When a couple get married, they are doing something they know nothing about. And, from all indications, when they have tried it more than once, they know no more about it the second time than they did the first." ...

"Where people are having trouble with marriage, it is because they are expecting it to run on automatic. They think it will hang together through no effort of their own; unfortunately, it won’t. It has to be created."

One part of the wedding vows is that you agree not to let a day dawn if there is a break in that ARC, in other words, if you have an upset, don't let it sit, but instead bring it up with the spouse and resolve the break in Affinity, Reality or Communication before closing out the day.

"Scientology founder L. Ron Hubbard isolated the three elements that make up Understanding: A is Affinity—which is the degree of closeness, liking or affection one has for something or someone; R is Reality—which is what we agree on; and C is Communication—which is of course the interchange of ideas between two people.

If you increase any one of these points in your dealing with another person you will have greater understanding with that person. Understanding is, of course, everything in a successful relationship.

NVL - Oracle SQL Null Field Handling

An often overlooked function that comes in very handy is the NVL function in Oracle. This allows you to replace a query result that returned NULL, with a value, such as 0. Here is the syntax:

SELECT NVL(fielda, 0) FROM table;

This will always give you either a value or a "0". This will never return a NULL.

Here is a link for further details on this function:

NVL Function

Oracle Wildcards

When searching using wildcards in Oracle SQL the most common use is the "%" character which searches for any number of characters. However a useful but less used wildcard is the "_" character which searches for any single character. These can be used in combination to form complex querying.

If you need to query for something that has a wildcard character in it, use the ESCAPE syntax as follows:

SELECT * FROM table WHERE field LIKE '%\%%' ESCAPE '\'

This will treat the '%' as a literal instead of as a wildcard.

Here is another good oracle sql resource:

Puget Sound Oracle Users Group