Re: Is it possible to create such a view?

From: fredericks <free101_at_picusnet.com>
Date: Sat, 17 Apr 1999 11:07:05 -0400
Message-ID: <3717fa44@news.itribe.net>

Johnson

>I wonder if it is possible for me to create such a view?
YES! Create a stored (server) function that does the calculation.

Create a view based on the ztable distinct c1 and the function calculated result.

Again

See functional magic paper by John C. Lennon at:

http://members.aol.com/jomarlen2/oraclesh.html

See Mary Lett's paper: Using Modifiable Join Views in Forms

at: WWW.ODTUG.COM

 1998 Handouts

    Developer

The funtion and views below should do what you want if I understand your logic requirements.

create function sflag
return varchar2

  (in_c1 in varchar)
   kt_f constant varchar2(1) := upper(f);
   kt_t constant varchar2(1) := upper(t);

   --
   cursor zt_cur

      (in_c1 in varchar)
   is

      select c2, c3
      from  ztable
      where  c1 = in_c1
      ;

   v_return boolean := true; -- initilize to true for cumultive *and* logic below

   v_rows_found boolean := false;
   --

• local function function tf_to_bool (in_tf in varchar2) is begin -- tf_to_bool if upper(in_tf) = kt_t then return true; elsif upper(in_tf) = kt_f then return false; end if; end tf_to_bool ; -- begin --sflag
• loop through all records for specified c1 for zt_rec in zt_cur (in_c1 => in_c1) loop
  • convert c2 c3 to boolean
  • logically *AND* all rerieved c2 , c3 values v_return := v_return and nvl( tf_to_bool(in_tf => zt_rec.c2), false ) and nvl( tf_to_bool(in_tf => zt_rec.c3), false ); v_rows_found := true; end loop; -- if not v_rows_found then
  • explictly control for case of no c1 records return kt_f; else
    • convert boolean back to T/F if v_return then return kt_t; elsif not v_return then return kt_f; end if; end if; end sflag ;

create or replace view ztable_distinct_c1_view as select distinct c1
from ztable;

create or replace view ztable_sflag_view as select c1, sflag(c1) sflag
from ztable_distinct_c1_view
;

Note there is probably a way to do this with a single view - but this should work.

Again HTH

FUNCTIONALy yours...

Mark

Johnson Chao wrote in message <7esgmk$b5m$1_at_nnrp1.dejanews.com>...
>Hi,
>I want to create such a complex view from one table as followed:
>
>ztable
>c1 c2 c3
>1 T F
>1 T T
>1 F F
> -- 3 sets data for 1
>2 T T
>2 T T
>2 T T
>2 T T
>2 T T
> -- 5 sets data for 2
>3 F F
>3 F T
>3 F F
>3 T F --4 sets data for 3
>I want to create a view from ztable with a new column 'sflag' whose
>value depends on the column 'c1''s data set.its value is T only when
> both c2 and c3 are true for the whole data set.
>
> The ztable_view I wish to create is below:
> ztable_view
> c1 sflag
> 1 F -- not all 3 sets of c2,c3 data are TRUE
> 2 T --all 5 sets of c2,c3 data are TRUE
> 3 F -- not all 4 sets of c2, c3 data are TRUE
>
>I wonder if it is possible for me to create such a view?
>
>Johnson
>Ctc/Japan
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Sat Apr 17 1999 - 10:07:05 CDT