Re: Insert Statement

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)

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

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 Received on Thu Nov 06 2008 - 09:58:43 CST