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 9i+ hierarchical query)

Date:11/21/07
Author:Christoph Linder
URL:n/a
Comments:0
Info:http://www.oracle.com/technology/documentation/database.html
Score: (3.00 in 1 votes)
/*
  Made for Oracle 9i and upwards.
  Uses the "Hierarchical Query" feature to generate bottles onna wall.
  The pseudo-column "level" denotes the current recursion depth. 
  The maximum bottle count (a.k.a. recursion depth) is 255 (oracle 9i, iirc), so you better serve
some ale :)
*/
select 
 decode(wall.bottles, 0, 'No more bottles of beer on the wall, '||
                         'no more bottles of beer.'||chr(10)||
                         'Go to the store and buy some more, '||
                         '99 bottles of beer on the wall.',
                      replace(replace('$bottles of beer on the wall, '||
                                      '$bottles of beer.'||chr(10)||
                                      'Take one down and pass it around, '||
                                      '$remaining_bottles of beer on the wall.',
                           '$bottles', wall.bottles||' bottle'
                                       ||case 
                                         when wall.bottles > 1 then 's' 
                                         end ),
                           '$remaining_bottles', case wall.bottles-1 
                                                 when 0 then 'no more bottles'
                                                 when 1 then 'one more bottle'
                                                 else wall.bottles-1||' bottles' 
                                                 end ) 
 ) as beersong_stanza
from (
       select level-1 as bottles
       from dual -- system table containing exactly one row
       connect by level <= 100
       order by bottles desc
      ) wall

Download Source | Write Comment

Alternative Versions

VersionAuthorDateCommentsRate
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
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: