2 Replies Latest reply: Mar 8, 2013 12:44 AM by Manik RSS

    Other methods of writing such SQL

    Manik
      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
          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
            :) Nice!!!
            Tested your code till 100 and its working fine.

            Cheers,
            Manik.

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