Compare Char Type with Char Type [message #618064] |
Mon, 07 July 2014 07:52 |
|
bluetooth420
Messages: 146 Registered: November 2011
|
Senior Member |
|
|
Hi all,
I am developing a function in oracle forms. I am facing the following problem.
I have a table like this
create table range
(para varchar2(100),
para varchar2(100)
);
insert into range (vchar, para) values ('1,2,3,4,5,6,7,8,10' , 'catida');
The part of code i am writing in the function is as follow
Select vchar
into mcatidA
from range
where para='catida';
--mcatid is numeric having value 5 in it
if to_char(mcatid) in (mcatida) then
return(1);
else
return(2);
end if;
Its returning 2 while i am expecting 1 as 5 is present in 1,2,3,4,5,6,7,8,10.
Any thoughts that how can i achieve my objective.
Side note: Its working perfectly if insert into range (vchar, para) values ('5' , 'catida');
Thanks
|
|
|
|
Re: Compare Char Type with Char Type [message #618092 is a reply to message #618066] |
Mon, 07 July 2014 12:16 |
|
bluetooth420
Messages: 146 Registered: November 2011
|
Senior Member |
|
|
Thanks for Guidance
I have developed the Function as i am using 10.2.0
Here is the function as per my OWN requirement
CREATE OR REPLACE FUNCTION CATID_YN (msource char, mtarget char) return number is
mans number;
mcatidA varchar2(50);
begin
if mtarget='A' then
Select ltrim(rtrim(vchar))
into mcatidA
from range
where para='catida';
end if;
Select nvl(count(*),0)
into mans
from (
select distinct regexp_substr(mcatidA,'[^,]+', 1, level) str
from dual
connect by regexp_substr(mcatidA, '[^,]+', 1, level) is not null)
where str=msource;
return(mans);
end;
/
This seems to be working.
Thanks for your words!
|
|
|
|