 | |
#31
 
| |||
| |||
|
Re: Insert Statement -
11-07-2008
, 01:03 PM
|
On Nov 6, 9:58*am, ddf <orat... (AT) msn (DOT) com> wrote: On Nov 6, 9:41*am, artme... (AT) gmail (DOT) 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! |
http://asktom.oracle.com/pls/asktom/...:6263249199595
jg
--
@home.com is bogus.
"NOTE: in mysql, where my_value = TRUE is precisely identical to where
my_value = 1 - boolean my butt, it is a *number*" - Tom Kyte
#32
| |||
| |||
|
|
On Nov 6, 9:58*am, ddf <orat... (AT) msn (DOT) com> wrote: On Nov 6, 9:41*am, artme... (AT) gmail (DOT) 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! |
http://asktom.oracle.com/pls/asktom/...:6263249199595
jg
--
@home.com is bogus.
"NOTE: in mysql, where my_value = TRUE is precisely identical to where
my_value = 1 - boolean my butt, it is a *number*" - Tom Kyte
#33
| |||
| |||
|
|
On Nov 6, 9:58*am, ddf <orat... (AT) msn (DOT) com> wrote: On Nov 6, 9:41*am, artme... (AT) gmail (DOT) 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! |
http://asktom.oracle.com/pls/asktom/...:6263249199595
jg
--
@home.com is bogus.
"NOTE: in mysql, where my_value = TRUE is precisely identical to where
my_value = 1 - boolean my butt, it is a *number*" - Tom Kyte
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.