99 Bottles of Beer

Voting

Bookmarking

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

Version	Author	Date	Comments
Oracle 9i+ hierarchical query	Christoph Linder	11/21/07	0
DB2 / ANSI	Kent Olsen	11/29/05	1
MS SQL 2000	David Teviotdale	09/07/05	4
MS-Transact-SQL 8.00	Karl	07/06/05	0
Self contained Oracle 9i+ Version	Chris Farmer	11/10/05	0
MS SQL 2005	Johan Åhlén	09/28/09	1
CROSS JOIN - UNPIVOT - SQL SERVER 2008	Nick Jacobsen	09/18/11	0
standard version	Rob van de Pol	04/20/05	0
Oracle, DBA Version	Thomas Rein	02/03/07	0
Produces the exact lyrics, ORACLE	Ernst Madsen	09/23/05	0

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!

one program in 1500 variations