Re: Can I create a View based on conditions?

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 28 Jan 2003 07:53:19 -0800
Message-ID: <2687bb95.0301280753.2c5012b8_at_posting.google.com>

maheshvd_at_rediffmail.com (Mahesh) wrote in message news:<3b36ea6e.0301280338.30a3df08_at_posting.google.com>...
> Can I do something like this?
>
> CREATE OR REPLACE VIEW MYVIEW AS
> SELECT a.Year Year,
> DECODE(a.CODE1= d.CODE1,TRUE,d.time,a.HINMOKU_CODE) x,
> DECODE(a.CODE1= d.CODE1,TRUE,d.time,a.HINMOKU_CODE) y
> (a.pp/ d.qq * d.rr) g1
> FROM table1 a, table2 b, table3 c, table4 d, table5 e
> WHERE a.CODE1 = b.CODE1(+)
> AND a.CODEE2 = e.CODE2;
>
>
> Is there any alternative?
>
> Thanks in advance.

It might help if you explained what you want to do as your code appears to be a five table join with where cluase conditions for only 3 of the 5 tables, which is generally not a good thing. You are also either missing a comma or trying to perform some type of operation I do not understand.

The Case statement might be what you are looking for:

UT1> select * from marktest;

FLD1 FLD2 FLD3

---------- ---------- ---------
one                 1 04-DEC-02
two                 2 03-DEC-02
three               3 02-DEC-02

UT1> select case when a.fld1 = b.fld1 then 'match' else 'no match' end   2 from marktest a, marktest b
  3 /

CASEWHEN

---

match
no match
no match
no match
match
no match
no match
no match
match

9 rows selected.

HTH -- Mark D Powell -- Received on Tue Jan 28 2003 - 16:53:19 CET