Re: Problem with a view

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Mon, 10 Sep 2001 11:04:29 +0100
Message-ID: <3b9c9036$0$8514$ed9e5944@reading.news.pipex.net>

Your join between all_tab_columns and all_coll_comments is on column_name. If you have the same column_name in more than one table, highly likely, you will get multiple rows returned. I believe what you want is

WHERE
b.table_name like 'PREFIXE%'

AND b.column_name = c.column_name
AND B.TABLE_NAME = C.TABLE_NAME
AND B.OWNER = C.OWNER

HTH

--
Niall Litchfield
Oracle DBA
Audit Commission UK
"Ben-oui" <aslamette_at_free.fr> wrote in message
news:4L%m7.1697$tP.2105373_at_nnrp5.proxad.net...


>

> I create a view by :

>

>

>

>

>

> CREATE OR REPLACE VIEW VIEWCOLONNES

>   (nomtable,

>    nomcolonne,

>    typedonnees,

>    nullable,

>    taille,

>    precisionchamp,

>    scale,

>    commentaire)

>

> AS SELECT

>   b.table_name,

>   b.column_name,

>   b.data_type,

>   b.nullable,

>   b.data_length,

>   b.data_precision,

>   b.data_scale,

>   c.comments

>

>   FROM

>     sys.all_tab_columns b,

>     sys.all_col_comments c

>

>   WHERE

>        (b.table_name like 'PREFIXE%'

>     AND b.column_name = c.column_name)

>

>

>   ORDER BY b.table_name

>

>

>

> When I execute the view, Oracle returns a column is displayed several time

> and I don't understand why ?

>

> And, it returns the VIEWCOLONNES columns in this query ! Why ?

>

>

>

>

>

Received on Mon Sep 10 2001 - 05:04:29 CDT