Home » SQL & PL/SQL » SQL & PL/SQL » How to identify last column added
How to identify last column added [message #299267] Mon, 11 February 2008 01:37 Go to next message
suhasbhide
Messages: 9
Registered: February 2008
Junior Member
Hi Friends...

I have taken over an old monitoring system where daily data is imported to a table in new column. e.g. data for y'day will add in new column DM_09_02. Since, the column header will change daily (not sure how as I am still waiting for full access), I want to know how to find out this last added column in the table. Any clues pls.

Thanks.
Re: How to identify last column added [message #299269 is a reply to message #299267] Mon, 11 February 2008 01:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"desc mytable" in SQL*Plus.

Regards
Michel
Re: How to identify last column added [message #299273 is a reply to message #299269] Mon, 11 February 2008 02:59 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Or, with a sql-query: find the column with the highest column_id (for that table) in user/all/dba_tab_columns

[Updated on: Mon, 11 February 2008 03:00]

Report message to a moderator

Re: How to identify last column added [message #299274 is a reply to message #299269] Mon, 11 February 2008 03:00 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You might query the USER_TAB_COLUMNS view and see which column has the highest 'column_id', such as
select t.column_name 
from user_tab_columns t
where t.table_name = 'YOUR_TABLE_NAME'
  and t.column_id = (select max(t1.column_id)
                     from user_tab_columns t1
                     where t1.table_name = t.table_name
                    ); 
Re: How to identify last column added [message #299279 is a reply to message #299274] Mon, 11 February 2008 03:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If it is in a PL/SQL procedure, query in descending order and only fetch the first row.

Regards
Michel
Re: How to identify last column added [message #299379 is a reply to message #299267] Mon, 11 February 2008 15:11 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
And since no one else said it, your design is horrible. Using a new column to hold daily totals is not a good idea. Have a child table that holds the totals.
Re: How to identify last column added [message #299963 is a reply to message #299267] Wed, 13 February 2008 20:16 Go to previous messageGo to next message
suhasbhide
Messages: 9
Registered: February 2008
Junior Member
Thanks to all. I got the answer from user_tab_columns.

The table structure is wrong. But, that what was found logical by my predecessor and now I will have task to revamp it.

Re: How to identify last column added [message #300017 is a reply to message #299963] Thu, 14 February 2008 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The table structure is wrong. But, that what was found logical by my predecessor and now I will have task to revamp it.

I think your likely are wrong.
Column order does not matter for your application as you MUST always name the columns in your statements.

Regards
Michel
Re: How to identify last column added [message #300328 is a reply to message #299267] Fri, 15 February 2008 01:21 Go to previous messageGo to next message
suhasbhide
Messages: 9
Registered: February 2008
Junior Member
Michel...

Nope. My table has got 2 columns fixed and other columns are added as new data for the data is inserted. So, if I need to find the latest data available, I would not know if the last data is of 14-feb or 15-feb (the data loading may be slow or has failed). Hence, I need to find the data from the 2 fixed columns (that never change) and the data from the last added column which will be latest data.

Suhas
Re: How to identify last column added [message #300330 is a reply to message #300328] Fri, 15 February 2008 01:26 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
That desigm mixes up columns and rows.
Re: How to identify last column added [message #300333 is a reply to message #300328] Fri, 15 February 2008 01:45 Go to previous message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your design is wrong.
Table structure is fixed and does not depend on data.
When data come you add rows not columns.

You change the tables structure when application upgrade.
Do you change your program when you insert new data?
Do you have a function for which you add parameters each time new data come?

Regads
Michel
Previous Topic: MERGE STATEMENTS
Next Topic: numeric overflow error
Goto Forum:
  


Current Time: Wed Dec 07 04:52:00 CST 2016

Total time taken to generate the page: 0.05506 seconds