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, no redundancy, with recursion)

Date:05/17/06
Author:Joachim Banzhaf
URL:n/a
Comments:0
Info:http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp
Score: (2.33 in 3 votes)
with maxbottles( num ) as
(
        values 99
),
strings( bottle, ofbeer, onthewall, comma, period, newline ) as
(
        values (' bottle', ' of beer', ' on the wall', ', ', '.', X'0A0D') 
),
count( num ) as 
(
        select num from maxbottles

        union all

        select num - 1 from count where num > 0
),
zero( num, bign, smalln, omore, store ) as
(
        values (0, 'N', 'n', 'o more', 'Go to the store and buy some more')
),
one( num, singular ) as
(
        values (1, '')
),
bottles( num, bign, smalln, omore, store, singular ) as
(
        select
                count.num, bign, smalln, omore, store, singular
        from
                count
                left outer join zero on count.num = zero.num
                left outer join one on count.num = one.num
)
select
        coalesce(bottles1.bign concat bottles1.omore, rtrim(char(bottles1.num)))
        concat bottle
        concat coalesce(bottles1.singular, 's')
        concat ofbeer
        concat onthewall
        concat comma 
        concat coalesce(bottles1.smalln concat bottles1.omore, 
                        rtrim(char(bottles1.num)))
        concat bottle
        concat coalesce(bottles1.singular, 's')
        concat ofbeer 
        concat period 
        concat newline
        concat coalesce(bottles1.store, 'Take one down and pass it around')
        concat comma
        concat coalesce(bottles2.smalln concat bottles2.omore, 
                        rtrim(char(coalesce(bottles2.num, 
                                            (select num from maxbottles)))))
        concat bottle 
        concat coalesce(bottles2.singular, 's')
        concat ofbeer
        concat onthewall
        concat period 
        concat newline as "Lyrics of ""99 Bottles of Beer"""
from 
        strings,
        bottles as bottles1
        left outer join bottles as bottles2 on bottles1.num = bottles2.num + 1
order by
        bottles1.num desc
;

Download Source | Write Comment

Alternative Versions

VersionAuthorDateCommentsRate
Oracle 9i+ hierarchical queryChristoph Linder11/21/070
DB2 / ANSIKent Olsen11/29/051
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
Produces the exact lyrics, ORACLEErnst Madsen09/23/050

Comments

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: