Boolean in Decode

[james777]

How to return boolean value in decode statement....

select decode(a.col1,b.col1,true,false) from dual;

Values returned by decode are characters. Is there a way to
return boolean true false ..

Thanks
Jim.

 

[SantaMufasa]

James,

What did you hope to do with the results of the DECODE? Since a boolean result is not printable, a SELECT cannot display a boolean result. Obviously, you can display 'TRUE' or 'FALSE', but you cannot display TRUE or FALSE.

You can create a PL/SQL function that returns a boolean value:

create or replace function bool (bool_in varchar2)
return boolean is
begin
	if upper(bool_in) = 'T' then
		return true;
	elsif upper(bool_in) = 'F' then return false;
	else	return null;
end if;
end;
/

...but if you tried to "SELECT bool('T') from dual", you would receive a run-time error, "PLS-382: expression is of wrong type".

I hope this helps,

Mufasa
(aka Dave of Sandy, Utah, USA @ 00:26 (08Jul04) UTC (aka "GMT" and "Zulu"), 17:26 (07Jul04) Mountain Time)

 

[james777]

Alright Thanks Mufasa.
All .. One more Q' on Upsert , so called Merge..

How to trap exception errors ( value error, inserted value too long while inserting) in 9i Merge Statement..

Merge into tab1 a
using ( select col1, col2 from tab2) b
on (a.col1 = b.col1)
when matched then
update
set ...
when not matched then
insert...

How to trap the expections in Merge.. Is there a way or better to use the traditional Update/Insert vv..

 

[HimanB]

Hi,
You can also do this as follows:

Update <TABLE>
Where ......

If sql%rowcount=0 then
 Insert into <TABLE> values(.....);
End If;

HTH
Regards
Himanshu