Re: Booleans

Depends.
I use 'Y','N' in address records for OK TO MAIL.

The default value is 'Y', which contributes to the index on this field. A null (implying 'N') is used so that NOs don't appear in the index. Then when I select where OK_TO_MAIL = 'Y', the index is very efficient (i.e., only records with an index record are selected)

When I need to do frequency counts
(i.e.,
select sum(decode(true_false_field, 0,1,0)) TRUES,

           sum(decode(true_false_field,1,1,0)) FALSES )
it is useful to use NUMBERs so I can do SUM()s.

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:8qt66a$1gl$1_at_soap.pipex.net...
> Does no-one else use a number datatype with zero and one as permitted
> values.
> "Reinier" <Reinier_Dickhout_at_hetnet.nl> wrote in message
> news:#XTjgrrJAHA.346_at_net025s...
> > This next statement however, works fine......
> >
> > 1 create or replace function test_boolean return varchar2
> > 2 as
> > 3 loc_var boolean;
> > 4 loc_date date;
> > 5 begin
> > 6 loc_var := TRUE;
> > 7 if loc_var then
> > 8 select sysdate
> > 9 into loc_date
> > 10 from dual;
> > 11 return('TRUE');
> > 12 else
> > 13 return('FALSE');
> > 14 end if;
> > 15* end test_boolean;
> > SQL> /
> >
> > Function created.
> >
> > SQL> select sysdate
> > 2 from dual
> > 3 where test_boolean = 'TRUE';
> >
> > So there is a datatype called boolean in PL/SQL, just not available for
 DDL.
> > So for DDL use a varchar2(1) with an attached domain ('Y','N').
> >
> > But thanks for the additional info :-)
> >
> > Steve McDaniels <steve.mcdaniels_at_sierra.com> wrote in message
> > news:8qlmqa$l86$1_at_spiney.sierra.com...
> > > SQL> create table test
> > > 2 (key number,
> > > 3 junk boolean);
> > > junk boolean)
> > > *
> > > ERROR at line 3:
> > > ORA-00902: invalid datatype
> > >
> > > on Oracle 8.0.6.
> > >
> > > er, ah, gee, maybe I'm misspelling it.
> > >
> > > "Reinier" <Reinier_Dickhout_at_hetnet.nl> wrote in message
> > > news:#p$6yT5IAHA.178_at_net025s...
> > > > Try BOOLEAN ;-)
> > > >
> > > > Reinier.
> > > >
> > > > Sandro PIAZZINI <sandro.piazzini_at_programmer.net> wrote in message
> > > > news:8qaqj4$7gq$1_at_news6.isdnet.net...
> > > > > Hello,
> > > > >
> > > > > What is the best datatype to treat Booleans (like Yes/No fields)
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >

>
> Received on Wed Sep 27 2000 - 12:40:38 CDT