real language

Bookmarking Digg Diigo DZone Earthlink Google
Windows Live LookLater Ma.gnolia Reddit Rojo StumbleUpon Technorati

Language SQL

(Oracle 9i+ hierarchical query)

Author:Christoph Linder
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 :)
 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'
                                         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

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
DB2, no redundancy, with recursionJoachim Banzhaf05/17/060
Produces the exact lyrics, ORACLEErnst Madsen09/23/050


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!




Security Code: