Re: Insert Statement

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! Received on Thu Nov 06 2008 - 10:08:00 CST