Home » SQL & PL/SQL » SQL & PL/SQL » Retrieve column names with aliases (Oracle 10g)
Retrieve column names with aliases [message #667174] Tue, 12 December 2017 11:50 Go to next message
ananya.saphira
Messages: 2
Registered: December 2017
Junior Member
Hi, I need to retrieve all the column names of the table for some of the columns i need it to be a aliases, can you please help me with hoe it can be achieved.

example if columns are name,age,attribute1 then I nee attribute1 one as an alias address.

i dont need data in the columns i just need to select and display all column names of the table.

Thank you.
Re: Retrieve column names with aliases [message #667175 is a reply to message #667174] Tue, 12 December 2017 11:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Column names can be retrieved using USER_TAB_COLUMNS view.
The rest of the post is a mystery for me:

Quote:
example if columns are name,age,attribute1 then I nee attribute1 one as an alias address.
What does this mean? If you want an alias then just give it, it can't be retrieved without being first defined and it can be retrieved only where it is defined.

Re: Retrieve column names with aliases [message #667176 is a reply to message #667175] Tue, 12 December 2017 12:02 Go to previous messageGo to next message
ananya.saphira
Messages: 2
Registered: December 2017
Junior Member
I have a table with name employee and it has columns name , age, attribute1.
Now i query the columns with
SELECT column_name
FROM all_tab_cols
WHERE table_name = 'employee';

the result will be
Column_name
-----------
name
age
attribute1

but i need it as

column_name
-----------
name
age
address

where i need attribute1 to be aliased as address
Re: Retrieve column names with aliases [message #667177 is a reply to message #667176] Tue, 12 December 2017 12:06 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
ananya.saphira wrote on Tue, 12 December 2017 10:02
I have a table with name employee and it has columns name , age, attribute1.
Now i query the columns with
SELECT column_name
FROM all_tab_cols
WHERE table_name = 'employee';

the result will be
Column_name
-----------
name
age
attribute1

but i need it as

column_name
-----------
name
age
address

where i need attribute1 to be aliased as address
How does dumb SELECT statement know or decide which value to change & how does it know or decide what the new value should be?

Why should the results not be as below?

column_name
-----------
height
weight
color
Re: Retrieve column names with aliases [message #667178 is a reply to message #667176] Tue, 12 December 2017 12:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And how Oracle will know you want "address" instead of... instead of what? the third column or the column named "attribute1"?
So there are 2 questions in my post.

Re: Retrieve column names with aliases [message #667179 is a reply to message #667178] Tue, 12 December 2017 12:41 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
iT'S SILLY BUT

SELECT DECODE(column_name,'ATTRIBUTE1','ADDRESS',column_name)
FROM all_tab_cols
WHERE table_name = 'employee';
Re: Retrieve column names with aliases [message #667180 is a reply to message #667179] Tue, 12 December 2017 12:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Unless it is column position. Smile
Re: Retrieve column names with aliases [message #667181 is a reply to message #667176] Tue, 12 December 2017 15:14 Go to previous message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
ananya.saphira wrote on Tue, 12 December 2017 13:02
I have a table with name employee and it has columns name , age, attribute1.
Now i query the columns with
SELECT column_name
FROM all_tab_cols
WHERE table_name = 'employee';
Using a lowercase name like this will not retrieve any rows for a properly named table (ie. a properly named and created table).

If you created the table initially with double quotes, then this is the way to do it, but that would have been a mistake in the first place.
Previous Topic: ORA-01502: index or partition of such index is in usable
Next Topic: Help for getting information where external table used in database
Goto Forum:
  


Current Time: Fri Mar 29 05:09:18 CDT 2024