CODEWITHSUNDEEP
sqloracle-databasewildcard
Alex Fields
Alex Fields

Reputation: 67

setting an alias after the * in sql

Is there a way to create an alias for different columns in ORACLE SQL after the Asterisk (*)? 

SELECT * 
FROM TABLE 
WHERE INDEX_1 = 5
AND INDEX_2 = 6

instead is there a way to set an alias after the *? 

SELECT * INDEX_1 AS INDECIES
FROM TABLE 
WHERE INDEX_1 = 5
AND INDEX_2 = 6
;

Upvotes: 1

Views: 2960

Answers (3)

BriteSponge
BriteSponge

Reputation: 1054

If you are using SQLPLUS you can use the COLUMN command to set the heading displayed for any column regardless of whether it is selected using asterisk or by name.

E.g. COLUMN col_123 HEADING 'Account Number'.

The SQLPLUS reference for this is here : https://docs.oracle.com/cd/B28359_01/server.111/b31189/ch12013.htm

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 143083

Though, a little bit of "mix" can also be used: a rowsource-qualified asterisk AND (i.e. not OR) a field list, such as

SQL> select d.*, e.ename
  2  from dept d, emp e
  3  where e.deptno = d.deptno
  4    and d.deptno = 10;

    DEPTNO DNAME          LOC           ENAME
---------- -------------- ------------- ----------
        10 ACCOUNTING     NEW YORK      KING
        10 ACCOUNTING     NEW YORK      CLARK
        10 ACCOUNTING     NEW YORK      MILLER
        10 ACCOUNTING     NEW YORK
        10 ACCOUNTING     NEW YORK

SQL>

Upvotes: 0

Cee McSharpface
Cee McSharpface

Reputation: 8725

No.

The syntax of the SELECT statement's select_list part in oracle grammar is this:

{ *
  | { query_name.*
      | [ schema. ]
        { table | view | materialized view } .*
      | expr [ [ AS ] c_alias ]
    }
    [, { query_name.*
       | [ schema. ]
         { table | view | materialized view } .*
       | expr [ [ AS ] c_alias ]
       }
    ]...
}

That means, either asterisk and nothing else, or rowsource-qualified asterisk(s) and discrete columns. No c_alias token possible after an *.

What you can do, is to combine asterisks and field lists, even when both parts reference the same table, like here:

SELECT
  TA1.*,
  TA1.Column1 AS ColumnX
FROM Table1 TA1

Using the asterisk is discouraged for various reasons. Use it for convenience in ad-hoc queries.

Upvotes: 1

Related Questions