Re: Insert Statement

On 6 nov, 17:08, artme..._at_gmail.com wrote:
> On Nov 6, 9:58 am, ddf <orat..._at_msn.com> wrote:
>
>
>
>
>
> > On Nov 6, 9:41 am, artme..._at_gmail.com wrote:
>
> > > Can you use a CASE statement in an INSERT clause with Boolean
> > > expressions?  Something like
>
> > > DECLARE
> > >   b boolean := TRUE;
> > > BEGIN
> > >   INSERT INTO a
> > >   VALUES (
> > >   CASE
> > >     WHEN b = TRUE THEN 'AB'
> > >     WHEN b = FALSE THEN 'CD'
> > >   END);
> > > END;
> > > /
>
> > > I've tried several syntax variations with no luck......
>
> > You can't with a values clause, but you can with a select:
>
> > SQL> create table yakmov(
> >   2          dling number,
> >   3          gortvor varchar2(9)
> >   4  );
>
> > Table created.
>
> > SQL>
> > SQL> create sequence yakmov_seq
> >   2  start with 1 increment by 1 nomaxvalue nocycle order;
>
> > Sequence created.
>
> > SQL>
> > SQL> create table xipperhenb(
> >   2          zew number,
> >   3          tromh number
> >   4  );
>
> > Table created.
>
> > SQL>
> > SQL> insert all
> >   2  into xipperhenb
> >   3  values (1, 1)
> >   4  into xipperhenb
> >   5  values (2, 1)
> >   6  into xipperhenb
> >   7  values (3, 2)
> >   8  into xipperhenb
> >   9  values (4, 2)
> >  10  into xipperhenb
> >  11  values (5, 2)
> >  12  into xipperhenb
> >  13  values (6, 2)
> >  14  into xipperhenb
> >  15  values (7, 1)
> >  16  into xipperhenb
> >  17  values (8, 2)
> >  18  into xipperhenb
> >  19  values (9, 1)
> >  20  select * from dual;
>
> > 9 rows created.
>
> > SQL>
> > SQL> commit;
>
> > Commit complete.
>
> > SQL>
> > SQL> insert into yakmov
> >   2  (dling, gortvor)
> >   3  select zew, case when tromh = 1 then 'AB' when tromh = 2 then
> > 'CD' end from xipperhenb;
>
> > 9 rows created.
>
> > SQL>
> > SQL> commit;
>
> > Commit complete.
>
> > SQL>
> > SQL> select * from yakmov;
>
> >      DLING GORTVOR
> > ---------- ---------
> >          1 AB
> >          2 AB
> >          3 CD
> >          4 CD
> >          5 CD
> >          6 CD
> >          7 AB
> >          8 CD
> >          9 AB
>
> > 9 rows selected.
>
> > SQL>
>
> > David Fitzjarrell
>
> Thanks David.  But, not with a simple PLSQL variable?  So, If I have a
> variable of BOOLEAN type and set it to True or False, I can't use that
> to insert into a table.....can I use it in a DECODE statement somehow?
>
> Thanks again!- Tekst uit oorspronkelijk bericht niet weergeven -
>
> - Tekst uit oorspronkelijk bericht weergeven -

What part of the sentence 'SQL does not know the boolean datatype' you don't understand?

--
Sybrand Bakker
Senior Oracle DBA