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

(Self contained Oracle 9i+ Version)

Date:11/10/05
Author:Chris Farmer
URL:n/a
Comments:0
Info:n/a
Score: (2.96 in 26 votes)
select
    CASE (a.aa * 10 + b.bb)
        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
            to_char(a.aa * 10 + b.bb) || ' bottles of beer on the wall, ' ||
            to_char(a.aa * 10 + b.bb) || ' bottles of beer. ' ||
            'Take one down and pass it around, ' ||
            to_char((a.aa * 10 + b.bb)-1) || ' bottles of beer on the wall.'
    END
from
    (select 0 aa from dual 
	union select 1 from dual 
	union select 2 from dual 
	union select 3 from dual 
	union select 4 from dual 
	union select 5 from dual 
	union select 6 from dual 
	union select 7 from dual
	union select 8 from dual 
	union select 9 from dual ) a,
    (select 0 bb from dual 
	union select 1 from dual  
	union select 2 from dual  
	union select 3 from dual 
	union select 4 from dual 
    	union select 5 from dual 
	union select 6 from dual 
	union select 7 from dual 
	union select 8 from dual 
	union select 9 from dual ) b
order by a.aa desc, b.bb 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
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

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: