Category Archives: Programming

Oracle Derived Columns In SELECT Statements

Database, Programming

It seems that using a derived column in a SELECT statement, without a table alias can cause problems.

The following base statement executes error free:

  1. SELECT *
  2. FROM MyTable

This statement errors with ORA-00936: missing expression:

  1. SELECT TO_DATE(B, 'DD-MON-YYYY') - TO_DATE(A, 'DD-MON-YYYY'), *
  2. FROM MyTable

This slightly modified statement will execute as expected:

  1. SELECT TO_DATE(B, 'DD-MON-YYYY') - TO_DATE(a, 'DD-MON-YYYY'), mt.*
  2. FROM MyTable mt

Initially, the idea of changing the order of the fields came to mind; it didn’t help. After adding in the alias and checking with a colleague – it worked. I don’t know why the derived column causes problems, however I thought it interesting none the less.

Oracle COALESCE Function

Database, Programming

Oracle implements many convenient functions to handle NULL values in the database, one of which is the COALESCE function. The Oracle COALESCE function accepts a varying length list of arguments and returns the first non-NULL value in the list. If all arguments in the list evaluate to a NULL value, then the COALESCE function will return a NULL value.

The standard format for the COALESCE function is:

  1. COALESCE(arg1, arg2, ..., argN)

There are many practical uses for the Oracle COALESCE function, two common actions include:

  1. Passing in a list of common items, which may or may not have data in them in a hope you’ll get back something which you can use.
  2. Generating a default value for a field which contains NULL values.

As a simple, yet practical example of how to use the COALESCE function; following demonstrates how to substitute in a default value in a SELECT statement when there are NULL values present:

  1. SELECT COALESCE(ADDRESS2, 'EMPTY') FROM ADDRESSES

Since there is always more than one way to achieve the same outcome, the following two statements will return the same results as the previous example:

  1. SELECT DECODE(ADDRESS2, NULL, 'EMPTY', ADDRESS2) FROM ADDRESSES
  2. SELECT CASE WHEN ADDRESS2 IS NULL THEN 'EMPTY' ELSE ADDRESS2 END FROM ADDRESSES

The next time you’re dealing with NULL values and you’d prefer a nicer looking result; consider giving the COALESCE function a try.

ASP.net 2.0: CSS Control Adapter Toolkit

Programming

Anyone that has worked with ASP.net that cares about web standards has no doubt lamented the HTML output of most web controls at some point. Thankfully with ASP.net 2.0, times are changing for the better. Scott Guthrie recently introduced the CSS Control Adapter Toolkit which provides a seamless way to improve the HTML output of your controls without effecting how they are used.

Being able to change the HTML output of a server control isn’t a new idea, you’ve been able to do it since day dot. Unfortunately, each method which allows you to override the HTML output has its own problems. In the case of a custom control, they are a lot of work to develop from the ground up and if you simply inherit a web control you then lose the ability to just ‘drag and drop’ and have it just work. Using CSS Control Adapters would allow you to change the HTML output of a web control, without creating a custom control or changing how you use the standard web controls.

To provide a simple example, consider a treeview style menu control. For the sake of an example, lets assume that the HTML output of that control by default is a convoluted nested mess of <table> tags. Since you’re a standards advocate and you care about the health of your clients browser, you’d really like to clean that up. You could implement a CSS Control Adapter to render you’re favourite treeview menu control using an elegant unordered list (<ul>). Better yet, maybe you don’t get consistent rendering of your fangdangle control on a particular platform/browser (a handheld device might be an excellent example). You could implement a handful of CSS Control Adapters for your web control and then customise which one is used through the use of a .browser file. The .browser files are used to register your adapters and also provides a simple way to define that adapter A is used in browsers H and I while adapter B is used in browsers E, F and G.

The significance of the CSS Control Adapter is that you can change the HTML output of a server control, without changing how the standard object is used. This means you could start implementing adapters for your existing code base, enable them and you haven’t had to change any of your existing code or how you were using the web controls! Pretty neat stuff I think and definitely a positive step in the right direction for ASP.net 2.0!

A Change Is As Good As A Holiday

Programming

For a while now I’ve felt like learning a new programming language, something different which doesn’t have the very familiar C-style syntax.

In the last year or so, there has been a lot of press around the ‘new’ programming language Python. Python is an interpreted dynamic object oriented language, in fact the language itself is implemented using objects such that a primitive like an integer is in fact an object. Python provides the ability to write command line, network aware, GUI and web based applications.

I’ve decided to give Python a serious look and over the coming weeks and months, I’ll be posting various code snippets and thoughts about it here for you to read and evaluate for yourself. Hopefully we’ll all learn something useful from it, happy hacking.

WordPress Plugin: Hicksdesign Style Archives

Blogging, Database, Programming, Software

The Hicksdesign Style Archives plugin now has a permanent URL.

Looking for a better way of displaying your blog archive list than a list of links to your monthly archive pages? There are many ways to display an archive list of posts, most common is a series of links to your ‘monthly archive’ pages. While perfectly functional, it just wasn’t working for me.

After looking around, I really liked the way that Jon Hicks displays his blog archive list. Using Jon’s method gives a little more substance to an otherwise fairly sparse page and the post titles break up the page nicely with their varying length.

Usage

  1. Download arl_hicksdesign_archives.zip
  2. Unzip the file locally
  3. Upload arl_hicksdesign_archives.php into your plugins folder (normally: /wp-content/plugins/)
  4. Enable the plugin via your administration console
  5. Edit your appropriate WordPress template file and add a call to arl_hicksdesign_archives()

Download

Zip: arl_hicksdesign_archives.zip
Source: arl_hicksdesign_archives.phps

ChangeSet

  • 2006-05-01:
    • Implemented get_permalink() to fix bug
    • Implemented get_month_link() to fix potential bug
  • 2006-04-29: Fixed spelling mistake in plugin name, arl_hicksdesign_archives().
  • 2006-04-26: Initial release.