Decode Question

[dbinfoweb]

Hello, I am jsut wondering how I can use decode in procedure in an insert into statement...here is what I try to accomplish...
insert into table select
field1,
field2,
(if field1 = this, and field2 = that) then 'thisthat' as field 3
from other table

Thanks in advance for any help

 

[THoey]

Didn't test it, just off the cuff, but I think this is close:

INSERT INTO MyNewTable (MyNewTable.Field1, 
                        MyNewTable.Field2, 
                        MyNewTable.Field3)
SELECT MyOldTable.Field1,
       MyOldTable.Field2,
       DECODE(MyOldTable.Field1, 
              'This',
              DECODE(MyOldTable.Field1,
                     'That',
                     'ThisThat',
                     ''),
              '')
FROM MyOldTable;

Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[dbinfoweb]

Great, this works. Thanks so much.
Now if I want to add this:
if field1 in ('a','b','c') and field2 not in ('m',n') then 'lmnopq' as field3. I am not sure how to decode this. Thanks again.

 

[ddrillich]

The case expression might be useful here.

Please have a look at:
thread186-711507

Regards,
Dan