asked on

How to Query a function whose parameter is a value from different table

I had this question after viewing Create Function which returns records from multiple tables..

I see this query from the above question:

select * from table(DepartmentEmployees('07/09/2017','07/20/2017'));

how to change the above to get the parameters from other table.

TableA ==> This table will only have one record as below, and I need to get those into above query.
=========
start_date|end_date
=================
07/19/2017|07/22/2017

I tried something like below but I'm getting error ORA--00936 Missing Expression.

select (select start_Date from tableA where rownum = 1) as start_date,
(select end_Date from tableA where rownum = 1) as end_date,
*
from table(DepartmentEmployees(start_date - 30, end_date) );

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

You should leave questions open a while longer to get alternative points of view. Some Experts that provide answers may not know the complete picture since they weren't involved in previous questions.

Even though you've already accepted this, since I was involved in the creation of your function I wanted to add that I wouldn't do it that way.

I would add logic to the function that would do the lookup from the table. You could easily add logic that if one of the input parameters were null, go look in the table for that value.

Here is the modified test case on how I would do this:

--drop table tab1 purge;
--create table tab1(start_date varchar2(10), end_date varchar2(10));
--insert into tab1 values('07/09/2017','07/22/2017');
--commit;
--
--
--drop table emp purge;
--create table emp(empno number, empname varchar2(10), deptno number, start_date date);
--
--insert into emp values(1,'Sam',1, to_date('07/18/2017','MM/DD/YYYY'));
--insert into emp values(2,'Andrew',2, to_date('07/10/2017','MM/DD/YYYY'));
--commit;
--
--drop table dept purge;
--create table dept(deptno number, deptname varchar2(10));
--insert into dept values(1,'Arts');
--insert into dept values(2,'Science');
--
--drop type myTable;

create or replace type myType as object (
	empno	number,
	empname	varchar2(10),
	deptname	varchar2(10),
	start_date	date
);
/

show errors

create or replace type myTable as table of myType;
/

show errors

create or replace function DepartmentEmployees(p_start_date in varchar2 default null, p_end_date in varchar2 default null) return myTable pipelined
is
	v_row myType := new myType(null, null, null, null);
	v_start_date	date;
	v_end_date		date;
begin
	select to_date(nvl(p_start_date,start_date),'MM/DD/YYYY'),
		to_date(nvl(p_end_date,end_date),'MM/DD/YYYY')
	into v_start_date, v_end_date
	from tab1;

	for i in (
		select * from emp e join dept d on e.deptno=d.deptno where e.start_date >= v_start_date and e.start_date <= v_end_date
	) loop
		v_row.empno := i.empno;	
		v_row.empname := i.empname;	
		v_row.deptname := i.deptname;	
		v_row.start_date := i.start_date;	
		pipe row(v_row);
	end loop;
end;
/

show errors

select * from table(DepartmentEmployees('07/09/2017','07/20/2017'));
select * from table(DepartmentEmployees);

Open in new window