SQL does not recognise the boolean datatype. You'll have to have the function return a different datatype
I have the function like as follows
Function Test_Bool (id number,name varchar2 ) return boolean as
for the above function i need to use as follows
select 'X' from dual where Test_Boo(1,'dddd') = 1 ;
but its show the error like invalid identifier
How to do this? just i want to check in where condition like as true
If you want to say:
where Test_Boo(1,'dddd') = 1
then you want Test_Boo to return a NUMBER, not a BOOLEAN.
If you can't change Test_Boo so that it returns a NUMBER, you can write a wrapper function that does:
CREATE OR REPLACE FUNCTION test_boo_num
( in_id IN NUMBER
, in_name IN VARCHAR2
test_boo_true BOOLEAN := test_boo (in_id, in_name);
return_val := 1;
ELSIF NOT test_boo_true
return_val := 0;
You can call the wrapper function from SQL statements like this:
where Test_Boo_num (1, 'dddd') = 1
I assume test_boo is actually more complicated than what you posted. If test_boo never returns UNKNOWN, then the wrapper function can be simplified a little.
Do like this,
create Function Test_Bool (id number,name varchar2 ) return number as
--if something then
select 'X' from dual where Test_Bool(1,'dddd') = 1 ;
If the functionality within the function test_bool results in a boolean, recreate the function returning a number and use this as the return clause
if it does not matter what datatype is used within the function, just use a number.
create or replace function test_bool return number
-- Do a lot of stuff which gives a boolean value to t_result
return case when t_result then 1 else 0 end;
Thanks To All ... Its Really Helpful ...