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

Blogger Sitemap for Google

If you are trying to create a sitemap of one of your blogs for google here is how you can do it:

Step # 6: You can add a Sitemap to your account to provide Google additional information about about your blog. Google will process your Sitemap and provide information on any errors in the Sitemaps tab as well your sitemap will be downloaded everyday to index your blog fast.
Select type as : Add General Web sitemap

Now you need to add Atom 0.3 feeds. Generally, you would use this format only if your site already has a syndication feed and this is the only way to add sitemap to your blogger.com account.

Paste url of your Atom feed: For example http://cyberciti.blogspot.com/atom.xml and click on Add Web sitemap:

Update: If you are using a new blogger beta system, use http://yourblogname.blogspot.com/rss.xml

You will get confirmation:
You have added a Sitemap to http://cyberciti.blogspot.com/. Reports may take several hours to update. Thank you for your patience!

And you are done and your blog will be now index very fast (depend upon your posting and content).

How to Rebuild Java from Class File? Use a Decompiler!

I ran into a situation where I couldn't find the latest source for a project I was working on. All I had was the executable jar file which was the latest version. I searched through my computer for the source code but couldn't find it anywhere. I must not have saved the last version or it get accidentally deleted. Then I discovered Java Decompilers and after searching for a few seconds on the web I found jad.exe - a simple decompiler that reads a compiled CLASS file and converts this into a java source file. Granted it is not perfect but through using this I was able to recover the work done and recreate my source file (through using jad as well as winmerge).

You can download jad here.
You can download winmerge here.

Grand Opening of Scientology London

A fellow coder from United Kingdom couldn't be reached last week as he was attending the grand opening of London's new Church of Scientology building. He is an old school programmer working mainly in Fox-Pro!?! He attended the opening and I heard it that even the rain couldn't stop the people from flooding into the place. See the photo below with the umbrella's!

Scientology restored two famous buildings in London recently and just held the grand opening of these two new beautiful buildings.

LONDON — More than 3,000 members and friends of the Church of Scientology of London packed Queen Victoria Street in the heart of London for the sensational grand opening of the new home of the UK’s oldest Scientology church.

Steps away from the Tate Gallery and St. Paul’s Cathedral, the beautifully restored historic building at 146 Queen Victoria Street now serves the growing membership of London-area Scientologists. Its opening marks the largest expansion for Scientology in the Church’s 50-year history in London.
...

“This new church packs the full body of L Ron Hubbard’s technologies as derived from the greater well of Scientology knowledge,” said Mr. Miscavige, “and, as such, it provides the full gamut of Scientology activities for the betterment of mankind.”

Fanfare and umbrellas at the opening of the new Church of Scientology of London.

- Photos of the Grand Opening on the Scientology London site

At the grand opening Mr. David Miscavige spoke and gave the opening address.

- Mr. David Miscavige, Official Biography

How to Delete a Column from a Table in Oracle SQL

Here is the syntax to delete one column from an existing table in Oracle SQL. As this is not used very often I am noting it here for quick reference.

     ALTER TABLE employee DROP COLUMN vacationPay;

Pretty simple.

How to Select Top Results in a Group By

The other day I ran into an interesting problem with Oracle SQL. I used to use Microsoft SQL Server and last year switched over to Oracle and had to adjust to the peculiarities of Oracle SQL. I had a report that gave the top 10 results of an aggregate query. In SQL this was simple and looked something like this:

SET ROWCOUNT 10
SELECT field1, field2, COUNT(*)
FROM table
GROUP BY field1, field2
ORDER BY COUNT(*) DESC

But when trying to do this in Oracle SQL with no "ROWCOUNT" feature, I was stumped at first. Then I came up with the following which is a bit of a hack, but works:

SELECT field1, field2, CNT
FROM (
SELECT field1, field2, COUNT(*) AS CNT
FROM table
GROUP BY field1, field2
ORDER BY COUNT(*) DESC
)
WHERE ROWNUM <= 10;

If anyone knows of a more standard approach, let me know.