CODEWITHSUNDEEP
sqloracle-databaseselectalias
Freeze
Freeze

Reputation: 237

How to add column alias to subquery in select statement?

I am trying to run a query where I am using subqueries in the select statement.
This works: 

select   
(select sysdate from dual),   
(select 'municipality' from dual),  
(select count(*) from municipality)  
from dual;

But the resulting columnnames are ugly and therefore I want to add the column aliases.

I am trying to run the following query:

select   
(select sysdate from dual) as sysdate,   
(select 'municipality' from dual) as tablename,  
(select count(*) from municipality) as count  
from dual;

This fails with the ORA 00923: From keyword not found where expected error. Somehow, I am missing something.

Any suggestions?

Thanks.

Upvotes: 5

Views: 10150

Answers (2)

Art
Art

Reputation: 5782

The only error you had in your code was using keywords sysdate and count as aliases, which is no-no... Using AS is optional also. This works as intended:

select   
 (select sysdate from dual)  s_date,            -- NOT sysdate
 (select 'municipality' from dual)  tablename,  
 (select count(*) from dual) as cnt             -- NOT count
 from dual;

It is OK to use count but in real world it is not advisable and pro-s would not use keywords.

Upvotes: 1

John Woo
John Woo

Reputation: 263693

try wrapping it with double quotes,

select
   (select sysdate from dual) as "sysdate",
   (select 'municipality' from dual) as "tablename",
   (select count(*) from municipality) as "count"
from dual;

SysDate is a reserved keyword. Although, COUNT is an aggregate function but it is permitted to be used.

Upvotes: 2

Related Questions