Thursday, November 09, 2006

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

Monday, November 06, 2006

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

sitemap

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).

Sunday, November 05, 2006

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.

Friday, November 03, 2006

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

Thursday, November 02, 2006

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.

Wednesday, November 01, 2006

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.