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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

SujithData ArchitectCommented:
Not possible in sql query.

Possible in pl/sql programs. But not worth(at all) doing it.
0
SujithData 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
johnsoneSenior 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.