This discussion is archived
4 Replies Latest reply: Dec 30, 2012 8:36 PM by 960796 RSS

Avoid Union

960796 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    select 'Week' ||to_char(rownum) wk
    from dual 
    connect by level <= 3;
    
    WK                                         
    --------------------------------------------
    Week1                                        
    Week2                                        
    Week3
  • 2. Re: Avoid Union
    960796 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Cool.. Thanks Jineesh

Legend

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