Re: To know tables' columns in a database

I recall what I try to get:

For each key column, I would like to get a row with
- table name of this column

• column name
• key name ie constraint name
• position within the key
• a type = 'Primary', 'Secondary' or 'Foreign'
• a target table only for foreign keys
• a target column

> You really need to get familiar with these views if you want to do > anything useful around Oracle in the long run.

Now, I have a select query which give me something as:

TABLE_NAME COLUMN_NAME CONSTRAINT_NAME TYPE

TABLE1      COL1         SYS_C00208068    Primary
TABLE1      COL2         SYS_C00208067    Foreign

I try to make:

SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, TYPE, /* but also two new columns

1st) if type = 'Foreign' then take a value (not a string as 'xxxx') in a certain table
2nd) if type = 'Foreign' then take another value in a certain table

*/

FROM
( SELECT ... /* what give me the previous table */ ) <---- ALPHA

I try to use DECODE and CASE ...

In fact, the main problem is that I cannot use twice "type column" which   is extracted from the the ALPHA select. The error if a use twice same column is "SQL error: Il n'y a plus de données à lire dans le socket" <-> There is any data to read in socket.

Any idea? Thanks by advance. Received on Fri Feb 20 2004 - 09:46:44 CST