How can I use COLUMN_ID instead of Column Name in a query.

There is a table with a name of a column which is abit long for a third party application(Ascential Datastage). I would like to use the Column ID instead of its name.
Is it possible?

LVL 1
diteps06Asked:
Who is Participating?
 
SujithConnect With a Mentor Data ArchitectCommented:
A workaround would be to create a view on your original table with a shorter column name. But still you cannot use numbers for column names.

Something like

create view test_vw
as
select this_is_a_long_column_name col1, this_is_another_long_column col2
from <your table>;
0
 
jazzIIIloveCommented:
Hi there;

SELECT COLUMN_NAME FROM DBA_TAB_COLUMNS
WHERE TABLE_NAME = '<your table name>'
and COLUMN_ID = '3';

Now you can query on the third column of your table .

Best regards...
0
 
jazzIIIloveCommented:
using user_tab_columns:
example like:
select COLUMN_NAME,COLUMN_ID from user_tab_columns
where TABLE_NAME='EMP'
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
diteps06Author Commented:
I would like to the Column Id number to be in the select clause.
If the Column has an ID number 2. How can I use this number in the select clause to get its value.
Let me give example
I have a table Tab1 with the following metadata and data informations.
Tab1
Column |   ID
  DIP          1
  EMP         2

 Tab1
  DIP   |   EMP
  RIS      SMITH
  HR       JOHN

To get the values of EMP this simple query would be use.
SELECT EMP FROM  TAB1

Instead of using EMP I would like to use its COLUMN_ID (2) in the SELECT clause
0
 
SujithData ArchitectCommented:
Not possible in sql query.

Possible in pl/sql programs. But not worth(at all) doing it.
0
 
jazzIIIloveCommented:

sujith80:
>>Not possible in sql query.

Isn't below possible? I think possible...Any objections?

using user_tab_columns:
example like:
select COLUMN_NAME,COLUMN_ID from user_tab_columns
where TABLE_NAME='EMPLOYEE'
0
 
johnsoneConnect With a Mentor Senior Oracle DBACommented:
jazzIIIlove,

That will give you the column name and column id.  You cannot put that in the select part of a query to select by column id.




An alternative would be to create a function that you pass in the primary key and the column id and it would return the value.  That could be used in a select statement.

I think the best solutions would be either renaming the column to a shorter name or creating a view.
0
 
SujithData ArchitectCommented:
jazzIIIlove:
If you read carefully, the question is to use the column_id instead of the column name in a query.
It is not possible in SQL. You may try to have workarounds; but those are not worth than typing the complete column name.

Best - You may create a view.
Renaming the column could break the code in Ascential, which are populating/accessing this table.
0
 
jazzIIIloveCommented:
sujith80:
johnsone:
yes...you are right...i miss it...sorry...

Best regards...
0
 
SujithData ArchitectCommented:
this is one of the funny "accepts" I have seen!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.