[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2478
  • Last Modified:

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?

0
diteps06
Asked:
diteps06
2 Solutions
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
sujith80Commented:
Not possible in sql query.

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

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
 
sujith80Commented:
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
 
sujith80Commented:
this is one of the funny "accepts" I have seen!
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now