Voting

Category

real language

Bookmarking

Del.icio.us Digg Diigo DZone Earthlink Google Kick.ie
Windows Live LookLater Ma.gnolia Reddit Rojo StumbleUpon Technorati

Language SQL

(DB2 / ANSI)

Date:11/29/05
Author:Kent Olsen
URL:n/a
Comments:1
Info:n/a
Score: (2.95 in 21 votes)
SELECT 
  CASE (bottlecount)
    WHEN 0 THEN 'No more bottle of beer on the wall, no more bottles of beer. ' ||
                'Go to the store and buy some more, 99 bottles of beer on the wall.'
    WHEN 1 THEN '1 bottle of beer on the wall, 1 bottle of beer. ' ||
                'Take one down and pass it around, no more bottles of beer on the wall.'
    WHEN 2 THEN '2 bottles of beer on the wall, 2 bottles of beer. ' ||
                'Take one down and pass it around, 1 bottle of beer on the wall.'
    ELSE 
      rtrim (cast((BottleCount) as char(2))) || ' bottles of beer on the wall, ' ||
      rtrim (cast((BottleCount) as char(2))) || ' bottles of beer. ' ||
      'Take one down and pass it around, ' ||
      rtrim (cast((BottleCount)-1 as char(2))) || ' bottles of beer on the wall.'
  END
FROM
(
  SELECT avalue * 10 + bvalue as bottlecount
  FROM
    (VALUES (9), (8), (7), (6), (5), (4), (3), (2), (1), (0)) a(avalue),
    (VALUES (9), (8), (7), (6), (5), (4), (3), (2), (1), (0)) b(bvalue)
) as valuelist;

Download Source | Write Comment

Alternative Versions

VersionAuthorDateCommentsRate
Oracle 9i+ hierarchical queryChristoph Linder11/21/070
MS SQL 2000David Teviotdale09/07/054
MS-Transact-SQL 8.00Karl07/06/050
Self contained Oracle 9i+ VersionChris Farmer11/10/050
MS SQL 2005Johan ┼hlÚn09/28/091
CROSS JOIN - UNPIVOT - SQL SERVER 2008Nick Jacobsen09/18/110
standard versionRob van de Pol04/20/050
Oracle, DBA VersionThomas Rein02/03/070
DB2, no redundancy, with recursionJoachim Banzhaf05/17/060
Produces the exact lyrics, ORACLEErnst Madsen09/23/050

Comments

>>  Mile said on 08/20/08 22:45:07

Mile Excelent code this one! top geek!

Download Source | Write Comment

Add Comment

Please provide a value for the fields Name, Comment and Security Code.
This is a gravatar-friendly website.
E-mail addresses will never be shown.
Enter your e-mail address to use your gravatar.

Please don't post large portions of code here! Use the form to submit new examples or updates instead!

Name:

eMail:

URL:

Security Code:
  
Comment: