This discussion is archived
2 Replies Latest reply: Mar 7, 2013 10:44 PM by Manik RSS

Other methods of writing such SQL

Manik Expert
Currently Being Moderated
Hi All,
--
Oracle 11g r2 version:

I tried to generate alphabet sequence like A-Z followed by AA-ZZ followed by AAA-ZZZ etc... using this query.

Can anybody suggest any other ways to do it in terms of BETTER performance.
WITH chrset AS
        (    SELECT CHR (ASCII ('A') + ROWNUM - 1) alph
               FROM DUAL
         CONNECT BY ROWNUM <= 26),
     t AS
        (SELECT alph FROM chrset
         UNION ALL
         SELECT a.alph || b.alph
           FROM chrset a, chrset b
         UNION ALL
         SELECT a.alph || b.alph || c.alph
           FROM chrset a, chrset b, chrset c
         UNION ALL
         SELECT a.alph || b.alph || c.alph || d.alph
           FROM chrset a,
                chrset b,
                chrset c,
                chrset d
         UNION ALL
         SELECT a.alph || b.alph || c.alph || d.alph || e.alph
           FROM chrset a,
                chrset b,
                chrset c,
                chrset d,
                chrset e),
     basetbl AS (SELECT ROWNUM r, alph FROM t)
SELECT r id, alph nm
  FROM basetbl
 WHERE r <= 100;
Cheers,
Manik.

Edited by: Manik on Mar 8, 2013 11:43 AM
  • 1. Re: Other methods of writing such SQL
    jeneesh Guru
    Currently Being Moderated
    select case when rownum <= 26 then chr (64 + rownum )
                when rownum <= 26*26 then
                     chr (64 + floor((rownum-1)/26) ) ||
                     chr (64 + decode(mod(rownum,26),0,26,mod(rownum,26)) )
           end  alph
    from dual
    connect by rownum <= 100;
    You can add to CASE if more than 26*26 is required..

    Edited by: jeneesh on Mar 8, 2013 12:02 PM
    Definitely more options are there - MODEL, RECURSION (With WITH clause).. You can try yourself and come back if you face any issues..
  • 2. Re: Other methods of writing such SQL
    Manik Expert
    Currently Being Moderated
    :) Nice!!!
    Tested your code till 100 and its working fine.

    Cheers,
    Manik.

    Edited by: Manik on Mar 8, 2013 12:14 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points