4 Replies Latest reply: Dec 30, 2012 10:36 PM by 960796 RSS

    Avoid Union

    960796
      Hi,

      Version oracle 11g

      Is there a way to avoid multiple UNION statements and get statitc values in single SQL?
      select 'Week1' as term from dual
      UNION
      select 'Week2' as term from dual
      UNION
      ....
      ....
      Some functionality like selecting from dynamic table column-rows.... like .. "select term from table(term(rowvalues('Week1', 'Week2'...'Week5')) from dual" or something like that

      Thanks,
      S
        • 1. Re: Avoid Union
          jeneesh
          select 'Week' ||to_char(rownum) wk
          from dual 
          connect by level <= 3;
          
          WK                                         
          --------------------------------------------
          Week1                                        
          Week2                                        
          Week3
          • 2. Re: Avoid Union
            960796
            Thank you Jineesh..sorry that I didn't post exact values that I need

            O/N
            1W
            2W
            1M
            2M
            3M

            Just looking for how to select the above in one single SQL without UNION

            Thanks
            S
            • 3. Re: Avoid Union
              jeneesh
              957793 wrote:
              Thank you Jineesh..sorry that I didn't post exact values that I need

              O/N
              1W
              2W
              1M
              2M
              3M

              Just looking for how to select the above in one single SQL without UNION

              Thanks
              S
              select decode(level,
                          1,'O/N',
                          2,'1W',
                          3,'2W',
                          4,'1M',
                          5,'2M',
                          6,'3M') wk
              from dual 
              connect by level <= 6;
              
              WK
              ---
              O/N 
              1W  
              2W  
              1M  
              2M  
              3M  
              
               6 rows selected
              • 4. Re: Avoid Union
                960796
                Cool.. Thanks Jineesh