Re: How do I create this view

dmarsha3 (dmarsha3_at_csc.com) wrote:
: Group :
 

: I have a table
 

: Table ADDR_TYP as
 

: id varchar(10),
: usrid varchar2(10),
: state varchar2(40),
: addr varchar2(40),
: zipcode varchar2(40);
 

: And I need to create a view
 

: ADDR_TYP_VW as
 

: id,
: usrid,
: log_id(has values 1 for state,2 for addr and 3 for zipcode);

: Table ADDR_TYP
 

: id usrid state addr zipcode
: 01 78 OH xyz 12345

: View ADDR_TYP_VW
: id usrid log_id
: 01 78 1 -- corresponds to state
: 01 78 2 -- corresponds to addr
: 01 78 3 -- corresponds to zipcode
 

: Any help is appreciated.

: Thanks in advance

: rama
: kramakri_at_csc.dayton.com

i dont know if i understood the question, but:

create view addr_typ_vw ( <- why the whacky abbreviation? ) as
( select id, usrid, 1

    from addr_typ
    where state is not null )
UNION
( select id, usrid, 2

    from addr_typ
    where addr is not null )
UNION
( etc
);

note i havent typed this in.. but you get the idea. hope it helps
Randy :)

--
    ..uu.           Randy DeWoolfson          ----------------------
  .?$" '?i     .                              I  randyd_at_cais.com   I
 .T^M  ._at_"    d9    .    .f   ,.un.  t     ,  I--------------------I
 "  Z :#"    M `8   U    <  .dP"``Y# `M   _at_"  I brandall_at_erols.com I
    &H?`    Xl _R   $5.  $  ?*    _at_   'P,#"   I--------------------I
  ,d#^*L   :RP'~$b  f`$L:M  Xf  .f'    dH`    I     ,\//.      X   I
    &  'M ,P    `E  M   "$  Mux~      n!`     I     |o o|          I
   dk   `h"       ' j     " y"       *~       I=oOO==(_)==Ooo======I