Function not returning values ( Sometimes??)

[DARK SETH]

Hi all

My function return the a customer address. Some customers has got 2 then it doesn't return any values. The cust with one value does return the correct value. How can I fix this. Here is my Script

Function

CREATE or replace FUNCTION get_add(acc_no IN varchar) 
   RETURN varchar 
   IS 
       C_CONTACT1_NEW VARCHAR(50);
 
 
BEGIN 
 
 
SELECT C_CONTACT1
into 
C_CONTACT1_NEW
 
FROM orders A
where a.consigneekey = acc_no
 
group by C_CONTACT1
 
; 
 
   RETURN(C_CONTACT1_NEW); 
 END;
 

Execute with this.

SELECT get_add('002358-C') FROM DUAL; 
 
 

[rski]

If some customers has more than one address then what should be returned, both of them? Tipical functions can return only one value so you will have to merge both addresses into one and return it or use pipelined functions. If you use pipelined functions you will be abel to get all values the function returns by querying it
select * from TABLE(<function_name>).
The pipelined function may look like that (be aware I didn't test it)

CREATE or replace FUNCTION get_add(acc_no IN varchar)  
 RETURN varchar  
PIPELINED
 IS  
       C_CONTACT1_NEW VARCHAR(50); 
BEGIN  
FOR C_CONTACT1_NEW IN SELECT C_CONTACT1 into C_CONTACT1_NEW 
  FROM orders A 
  where a.consigneekey = acc_no 
  group by C_CONTACT1 
LOOP
 PIPE row(C_CONTACT1_NEW);  
END LOOP 
RETURN
END;
 

[DARK SETH]

It must show both values. I tried creating the function with your code but got an error at the line

FOR C_CONTACT1_NEW IN SELECT C_CONTACT1 into C_CONTACT1_NEW


PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:

( - + case mod new null <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
reverse avg c


Any help in this would be appreciated.

[rski]

create type row_type as object ( i varchar2(50));
create type table_type as table of row_type;
 
 
create or replace function f_test(acc_no varchar)
return object_table_type
pipelined
as
C_CONTACT1_NEW VARCHAR(50);  
begin
 
 
FOR C_CONTACT1_NEW IN (SELECT C_CONTACT1 into C_CONTACT1_NEW  
  FROM orders A  
  where a.consigneekey = acc_no  
  group by C_CONTACT1)
loop
pipe row(object_row_type(C_CONTACT1_NEW.C_CONTACT1));
end loop;
return;
end;
 

But why do you need a function? You can return all values you need with simple sql query, the one you use in LOOP.