Re: simple select statement inquiry

--- Joseph Syjuco <joseph@asti.dost.gov.ph> wrote:
> table1
> empno varchar(9) not null
> peer varchar(9) not null references table2(empno)
> superior varchar(9) not null references
> table2(empno)
> 
> table2
> empno varchar(9)
> firstname varchar(20)
> lastname varchar(20)
> 
> what i want to do is get all entries in table 1 and
> transform peer and
> superior fields (which contains empno) into their
> respective firstname +
> lastname in one query
> 
> desired output
> empno             peer                   superior
> 1000        John Smith        Henry Dunst
> 2000        Juan dela Cruz        Pepe Smith

Create a stored function that will return the fullname
of the "peer" or "superior" given an employee number

CREATE OR REPLACE FUNCTION get_name(VARCHAR(9))
RETURNS VARCHAR(50)
AS'
DECLARE  v_empno ALIAS FOR $1;  v_last VARCHAR(20);  v_first VARCHAR(20);  v_fullname VARCHAR(50);
BEGIN  SELECT firstname,lastname  INTO v_first,v_last  FROM table2  WHERE empno=v_empno;  v_fullname := '''';
v_fullaname:= v_first || '' '' || v_last;  RETURN v_fullname;
 
END;'
LANGUAGE 'plpgsql';

then 
type the ff. SELECT stmt:

SELECT empno,get_name(peer),get_name(superior)
FROM table1;

ludwig lim

__________________________________________________
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.yahoo.com