Hi team,
I have a requirement like this.
create table valid_lovs (code_id int not null,lov_value int not null
,description varchar(256),status bit(1) not null default '1',constraint lov_pk
primary key (code_id,lov_value));
insert into valid_lovs (code_id,lov_value,description) values (1,1,'1000
downloads');insert into valid_lovs (code_id,lov_value,description) values
(1,2,'2000 downloads');insert into valid_lovs (code_id,lov_value,description)
values (10,1,'US Dollar');insert into valid_lovs
(code_id,lov_value,description) values (10,2,'Singapore dollar');insert into
valid_lovs (code_id,lov_value,description) values (20,1,'Audio');insert into
valid_lovs (code_id,lov_value,description) values (20,2,'Video');insert into
valid_lovs (code_id,lov_value,description) values (20,3,'Overlay');
insert into valid_lovs (code_id, lov_value,description) values
(1000,1,'IMPRESSION_LOV');insert into valid_lovs (code_id,
lov_value,description) values (1000,10,'CURRENCY_LOV');insert into valid_lovs
(code_id, lov_value,description) values (1000,20,'MEDIA_FORMAT');
I need to write 2 functions.
1) Find_LOV. In this function I will pass only a text message but should return
an array.
create or replace function find_lov_func(in p_1 anyelement, out p_2 anyarray)
as$$ select array[x.code_id, x.lov_value] from valid_lovs x, valid_lovs y
where y.description = $1 and x.code_id = y.lov_value;$$language sql;
select find_lov_func('CURRENCY_LOV'::text);
I should get an output of
{10,1}
{10,2}
instead I am getting
ERROR: return type mismatch in function declared to return text[]DETAIL:
Actual return type is integer[].CONTEXT: SQL function "find_lov_func" during
startup
Q) How will I resolve this. I need to get array of integer only.
2) get_lov function: In this function, I will pass a text field and I should
get an integer and the text as output
for example
create or replace function get_lov_func(in p_1 varchar) returns setof
valid_lovs as$$ select x.lov_value, x.description from valid_lovs x,
valid_lovs y where y.description = $1 and x.code_id =
y.lov_value;$$language sql;
ERROR: return type mismatch in function declared to return valid_lovsDETAIL:
Final SELECT returns character varying instead of integer at column 2.CONTEXT:
SQL function "get_lov_func"
Can somebody help me in this?
Regards
skarthi
_________________________________________________________________
Take a break and play crossword puzzles - FREE!
http://games.msn.com/en/flexicon/default.htm?icid=flexicon_
wlmemailtaglinemarch07
