Simple Insert in Oracle with Case Statement

Below is a sample code for insert statement here I have used "case statement" in select and "not exists" in where clause..

insert into table1 t1 (field1,field2)
(select field1, case when field2 = ' ' then field2
else to_char(to_number(field2)
end
from table2 t2
where t2.field1 = t1. field1
and not exists (select 1
from table3 t3
where t3.field1 = t2.field1));

please don't forget to visit: http://www.lendmyspace.com

Select in Oracle vs Select in Sybase

In Oracle I came across situation where I have a select statement in SP which executes fine when data IS Found. But crash when there is data is NOT Found. So if you have any place where you need to continue with the sp even when data is not found then do the exception block in your code when you unit test.
If you don’t do this your sp will crash and it will go to the end of your exception handler block.

Example:

begin
select myfield1 into v_myfield1
from mytable1
where myfield1= value1;
exception
when no_data_found then
v_myfield1:=NULL;
end;

I think in Sybase the select query will works fine if data is not found. But in oracle world you do need to take care of it.

Hope this helps!

SQL/PL Update Statement and Sub Update Statement Findings

OK. I have done the update using sub inner update statement as shown below.

But when ever code (A) is executed it crash in store procedure because the conditions are not meet and sql execute update statement.

(A)

update (select t1.field1, t2.field2

from table2 t2

where t1.field2 = t2.field2) myupdatedtable t3

set myupdatedtable.field1 = myupdatedtable.field2;

so I have changed to (B) this way it runs fine without any problem and does not crash.

(B)

update table1 t1

set t1.field1 = (select t2.field1

from table2 t2

where t1.field2 = t2.field2)

where exists (select t2.field1

from table2 t2

where t1.field2 = t2.field2) ;

Hope this helps!