This post originated from an RSS feed registered with Java Buzz
by David Rolfe.
Original Post: Oracle's DECODE funtion...
Feed Title: OrindaBlog - Orinda Software's Corporate Blog
Feed URL: http://www.jroller.com/rss/orindasoft
Feed Description: We're in the business of creating products that make it easier to work with Oracle, Java and JDBC. We want this blog to be something more than a stream of product-related propaganda - our intention is two-thirds of the content will be of general relevance to people who work with Oracle, Java and PL/SQL. Entries to this blog will be made as and when we have information that we think our readers would be interested in.
Oracle's decode function can be used to create arbitrary ORDER BY clauses. This is useful if you are
providing lists with the most frequently picked value at the top - e.g. a list of countries which starts with 'United States' and then turns into a conventional alphabetical listing. Your SELECT statement will have two things in the ORDER BY clause - first it sorts by a DECODE that turns your favoured value into '1' and all other values into '2' and then it sorts the rest of the list conventionally. The alternatives to using DECODE are to hard code the entire list (bad!) or add a numeric column to the underlying table that defines the sort order, which is a lot more work than a DECODE in an ORDER BY.
SQL> r
1 select country_name
2 from iso_countries
3* order by decode(country_name,'UNITED STATES',1,2), country_name
COUNTRY_NAME
--------------------------------------------------
UNITED STATES
AFGHANISTAN
ALBANIA
ALGERIA
AMERICAN SAMOA
ANDORRA
ANGOLA
ANGUILLA
ANTARCTICA
ANTIGUA AND BARBUDA
ARGENTINA
...
This behaviour is common for web sites where drop down lists have to be dynamic (the contents may change) but also require location or market specific default preferences.
While we're on the subject of lists of countries one really should think twice before including Bouvet Island one's list...
Previous Topic
