Re: conditional view

On 27 Mai, 20:49, Mariano <mariano.calan..._at_gmail.com> wrote:
> i've this view:
>
> CREATE OR REPLACE FORCE VIEW "ANM_FAMILIARE" ("ID_MAL", "MALATTIA",
> "ID_PAZ", "PARENTELA") AS
> SELECT id_mal, malattia, id_paz, parentela FROM anamnesi_ricovero
> NATURAL JOIN ricovero NATURAL JOIN malattie WHERE id_mal!=0
> /
>
> This works properly, except one case, when there is only one record
> and this record have id_mal==0, in this case ResultSet will be empty.
>
> Can I modify this view, so when resultSet is empty will be used the
> same upon query without the WHERE clause.

Well, there may be a better way. However, here is a quick solution (i.e. quickly written,
not quickly executed ;-)

CREATE OR REPLACE FORCE VIEW "ANM_FAMILIARE" ("ID_MAL", "MALATTIA", "ID_PAZ", "PARENTELA") AS
  SELECT id_mal, malattia, id_paz, parentela FROM anamnesi_ricovero NATURAL JOIN ricovero NATURAL JOIN malattie WHERE id_mal!=0 OR 0 = (SELECT count(*) FROM anamnesi_ricovero NATURAL JOIN ricovero NATURAL JOIN malattie WHERE id_mal!=0) / Received on Tue May 29 2007 - 09:58:02 CDT