3 Replies Latest reply: Feb 1, 2013 2:32 PM by Frank Kulash RSS

    different aliases with union all

    user650888
      create table test_g(a number, b varchar2(10),x date);
      
      insert into test_g values (1,'a',(to_date('01-JAN-2013','DD-MON-YYYY')));
      
      insert into test_g values (2,'b',(to_date('02-JAN-2013','DD-MON-YYYY')));
      insert into test_g values (3,'c',(to_date('03-JAN-2013','DD-MON-YYYY')));
      insert into test_g values (4,'d',(to_date('04-JAN-2013','DD-MON-YYYY')));
      
      commit;
      I want to see different aliases for different queries

      select a,b "this is one ",x from test_g
      where b = 'a'
      union all
      select a,b "this is two" ,x from test_g
      where b = 'b'

      I want to see this is one alias for result of first query for column b
      this is two alias for result of second query for column b
        • 1. Re: different aliases with union all
          Hoek
          Can you also post the expected result you want in a formatted way? (just like you did when posting your DDL/DML).
          Your current description of what you want could be interpreted in multiple ways...
          • 2. Re: different aliases with union all
            stefan nebesnak
            A union returns a set of results, you can add a column..
            SQL> select a,b, 'one' "this is",x from test_g
              2  where b in ('a','b','c')
              3  union all
              4  select a,b, 'two' ,x from test_g
              5  where b = 'd';
             
                     A B          this is X
            ---------- ---------- ------- -----------
                     1 a          one     1.1.2013
                     2 b          one     2.1.2013
                     3 c          one     3.1.2013
                     4 d          two     4.1.2013
             
            SQL> 
            • 3. Re: different aliases with union all
              Frank Kulash
              Hi,

              It's unclear what you want. Explain what you're trying to achieve, and post the exact results you hope to get from the given sample data. Use \
               tags, as explained in the forum FAQ {message:id=9360002}.
              
              A column in any result set has only one name.  The same column name always applies to all rows of the result set.
              Why would you want a column to have different names on different rows?  How would you use that?
              Perhaps what you want is 2 separate columns, like this:
              SELECT     a
              ,     CASE WHEN b = 'a' THEN b END     AS "this is one"
              ,     CASE WHEN b = 'b' THEN b END     AS "this is two"
              ,     x
              FROM     test_g
              WHERE     b     IN ('a', 'b')
              ;
              Output:
              ` A this is one this is two X
              ---------- ----------- ----------- -----------
              1 a 01-Jan-2013
              2 b 02-Jan-2013