Home » SQL & PL/SQL » SQL & PL/SQL » function or proedure / datamodel needs to create (oracle/report builder 6i)
function or proedure / datamodel needs to create [message #307322] Tue, 18 March 2008 08:13
CHA5798
Messages: 1
Registered: March 2008
Location: NL
Junior Member
Hi

I have to develop a monthly separation report based on the parameters of date & empno

conditions: 1) if date is 200703, I should display the empno details for JAN + FEB + MAR

i have monthly separtion tables like emp_200701,emp_200702,emp_200703,so on

Nw i have a query for getting the details for the jan month.

i would like to append the query for remaining months based on the date input..i wrote a function

create or replace function fun_bld_qry(num_in_mth number)return varchar2
is

qry_strng varchar2:= 'select * from emp_'||v_date
qry_union varchar2(20):=' UNION ';
qry_output

begin
for i in 2..num_in_mth
LOOP

qry_output :=qry_output||qry_union||qry_strng;
v_date := v_date +1;

end loop
return qry_output;
end fun_bld_qry;

/* my output qry will be..SELECT * FROM EMP_200701
UNION SELECT * FROM EMP_200702 UNION SELECT * FROM EMP_200703...

---the qry is appending nw ,but i have a size problem....please guide me on this..

and suggest me , is my approach is correct for the Monthly separation report
...THANKS IN ADVANCE.






Previous Topic: Needed a sql query for below scenario
Next Topic: ORA-01653: Unable to extend table
Goto Forum:
  


Current Time: Sun Dec 04 04:22:00 CST 2016

Total time taken to generate the page: 0.19578 seconds