Dynamic Column Name (in SQL Window)

[Tinti]

Hi

I have a query which I start in a sql window. The column name I can define with "as"

e.g. => select objekt_name as Valuetype from dual

Now I like to give a more complex column name. The name will be generated with another query

e.g. => select objekt_name as (select name from testtab where valuetyp = 'xxxx') from dual

Is it possible to do that in a sql window?

Thanks
Joerg

 

[Marco Kalter]

The "AS" clause allows you to define an alias for a field. I think the syntax you are looking for is something like this:

select emp.*,
       (select dummy from dual) as alias_for_dummy
  from emp

 

[Tinti]

Marco, thanks for the fast reply.

What I want is following:

Header => DATE_PER ID_Nbr X7777 - Value of Revenues

Row001 => 05.05.10 147112 5282.15
Row002 => 05.05.10 012345 185.87
Row003 => ............................
Row004 => ............................

The text of the bold part in the header line is stored in a separate reference table. With X7777 i get the used text.

 

[Marco Kalter]

This is not possible. The closest you can get is by using a union. For example:

select 'employees from department ' || dname as field_name
  from dept
 where deptno = 10
union all
select empno || ' ' || ename as field_name
  from emp
 where deptno = 10

 

[orca777]

The Question is not if SQL Window allows this but if Oracle SQL allows dynamic column aliases -> NO!

SQL> select 'X' AS ( SELECT 'Y' FROM DUAL ) FROM DUAL;

select 'X' AS ( SELECT 'Y' FROM DUAL ) FROM DUAL

ORA-00923: FROM keyword not found where expected

SQL>

Before the SQL is parsed (befor execution of it) the Column name must be set; even with dynamic SQL;

/Karl