Voting
Category
real language
Bookmarking
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
| Version | Author | Date | Comments | Rate |
|---|---|---|---|---|
| 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 | |
| DB2, no redundancy, with recursion | Joachim Banzhaf | 05/17/06 | 0 | |
| Produces the exact lyrics, ORACLE | Ernst Madsen | 09/23/05 | 0 | |
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!
Comments