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

(Oracle, DBA Version)

Date:02/03/07
Author:Thomas Rein
URL:n/a
Comments:0
Info:n/a
Score: (2.50 in 4 votes)
SELECT
CASE bottels
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.'
ELSE
bottels || ' ' || thebar || ' ' || lessbottels
END
FROM
(SELECT rownum -1 bottels,
   decode(rownum,    1,    'bottle', 2, 'bottle',   'bottles') || ' of beer on the wall ' ||
to_char(rownum -1) thebar,
   decode(rownum,    1,    'bottle', 2, 'bottle',   'bottles') || ' of beer. Take one down and pass
it around, ' || decode(to_char(rownum -2),    0,    NULL,    to_char(rownum -2)) || ' ' ||
decode(rownum -2,    0,    'no more bottles', 1, 'bottle',   'bottles') || ' of beer on the wall.'
lessbottels
 FROM dba_objects
 WHERE rownum < 101
 ORDER BY 1 DESC)
/

Download Source | Write Comment

Alternative Versions

VersionAuthorDateCommentsRate
Oracle 9i+ hierarchical queryChristoph Linder11/21/070
Self contained Oracle 9i+ VersionChris Farmer11/10/050
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
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: