Re: views...

This is a straight forward join, the question is: what condition in another table. Is there some join (outer?) that must be met or is the condition a value. For example, if the column is to represent the presence of a value in another table, then an outer join would work.

Assume the tables are:
 t1 (col1 number, col2 number, col3 number)  t2 (col1 number)

The view should have four columns (col1, col2, col3, col4). And col4 in the view should be:

    1 if col1 in t1 is found in col1 of t2  and

    2 if col1 in t1 is not found in col1 of t2 (or is NULL)

CREATE VIEW v1 (col1, col2, col3, col4) AS   SELECT t1.col1, t1.col2, t1.col3, DECODE(t2.col1,NULL,0,1)   FROM t1, t2
  WHERE t1.col1 = t2.col1 (+) ;

HTH
  James

In article <84gj33$6is$1_at_nnrp1.deja.com>,   maylee <mayleel_at_my-deja.com> wrote:
> I'm trying to create a view that contains all the columns for a given
> table, plus a column that has a 1 or 0 based on a condition of another
> table.
>
> I'm not finding anything in the Oracle documentation to help me, and I
> cannot get this to work...
>
> Any ideas???
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Dec 30 1999 - 16:08:52 CST