-
I'm still waiting for your reply simply_dba.
-
Try something like this
SQL> Create or replace type driver_object is table of number(15)
/
create or replace function driv_func return driver_object
as
myobj driver_object := driver_object();
begin
<<< your code to populate myobj >>
return myobj;
end;
/
then in report
SELECT sp.name, sp.acct_num, count(s.reg_num), count(c.claim_num), count(c.claim_num)/count(s.reg_num) as Ratio,
SUM(s.act_line_haul_amt)
FROM shipment_people sp, shipment s, claims c
WHERE sp.s_reg_num = c.reg_num(+)
AND sp.acct_num IN
(Select column_value acct_num from table (cast (driv_func as driver_object)))
AND sp.s_reg_num = s.reg_num
AND sp.acct_num between :P_From_Acctnum and :P_To_Acctnum
AND s.act_load_date between :P_From_Date and :P_To_Date
GROUP BY sp.name, sp.acct_num
ORDER BY sp.acct_num
/
Hope this helps
Experience is a hard teacher because she gives the test first, the lesson afterwards.
-
Hey Bonker,
I really appreciate your help. You helped me in other post. Thanks.
I used your example for this post. I have created the object type and the function and populating my myobj table in my driv_func. I also have that sub select in my report. Do i need to call my function in Before Report Trigger to get it populated or, sub select will call it as you are casting. I have put the sub select in my report query but there is no output my report is empty. I have everything below :
Code:
Create or replace type driver_object is table of number(15);
created.
create or replace function driv_func return driver_object
as
myobj driver_object := driver_object();
CURSOR ACTIVE_DRIVER IS
select driver_account_number
from driver
where contract_date < sysdate
and contract_expires > sysdate;
CURSOR ACTIVE_AGENTS IS
select account_num
from f01names2
where (account_num between 1 and 1900 OR account_num between 4000 and 6700);
begin
FOR CUR IN ACTIVE_DRIVER
LOOP
myobj := driver_object(CUR.DRIVER_ACCOUNT_NUMBER);
END LOOP;
FOR CUR IN ACTIVE_AGENTS
LOOP
myobj := driver_object(CUR.ACCOUNT_NUM);
END LOOP;
return myobj;
end;
/
Function created.
In my report query I have this, it seems it accepts it fine:
SELECT trim(substr(sp.name, 1, decode(instr(sp.name, ')'), 0, length(sp.name), instr(sp.name, ')')-4))), sp.acct_num, count(s.reg_num), count(c.claim_num), count(c.claim_num)/count(s.reg_num) as Ratio,
SUM(s.act_line_haul_amt)
FROM shipment_people sp, shipment s, claims c
WHERE sp.s_reg_num = c.reg_num(+)
AND sp.acct_num IN
(Select column_value acct_num from table (cast (driv_func as driver_object)))
AND sp.s_reg_num = s.reg_num
AND sp.acct_num between :P_From_Acctnum and :P_To_Acctnum
AND s.act_load_date between :P_From_Date and :P_To_Date
GROUP BY trim(substr(sp.name, 1, decode(instr(sp.name, ')'), 0, length(sp.name), instr(sp.name, ')')-4))), sp.acct_num
ORDER BY sp.acct_num
EDITED:
When I run this select statement in SQL PLUS. I would assume it should give me a list of account_number. I get this:
1* Select column_value acct_num from table (cast (driv_func as driver_object))
oracle@OLD.CVL> /
ACCT_NUM
----------
1539
Elapsed: 00:00:00.81
oracle@OLD.CVL>
EDITED
I also would like to know in my sub select acct_num is the alias or what. I'd appreciate your reply on this one. It would really help me accomplish things in a newer way. Thanks.
Last edited by see_one; 06-15-2005 at 03:22 PM.
-
I just checked even the count from both cursors is somewhere 3000. I don't know whats going on, man. I really want to complete this thing for learning, at least. Thanks a whole lot.
-
Okay. I know whats happening. Bonker you need to read my two posts before this one. I comment out one cursor and used just the first one. The count I gave you earlier was not the count it was a value in my object table. If I use the return the object outside my For Loop, as u used it, it returns the value in the first record.
So, I thought I should put it inside of the loop to keep returning the entire thing. Now, it return the last record. I don't know what's the deal with PL/SQL. I am just tired of figuring this small **** out. Damn it! I know, I have already posted so many times, u may reply whenever u have time. Thanks a lot.
-
create or replace function driv_func return driver_object
as
myobj driver_object := driver_object();
CURSOR ACTIVE_DRIVER IS
select driver_account_number
from driver
where contract_date < sysdate
and contract_expires > sysdate;
CURSOR ACTIVE_AGENTS IS
select account_num
from f01names2
where (account_num between 1 and 1900 OR account_num between 4000 and 6700);
begin
FOR CUR IN ACTIVE_DRIVER
LOOP
myobj.extend;
myobj(myobj.count) := CUR.DRIVER_ACCOUNT_NUMBER;
END LOOP;
FOR CUR IN ACTIVE_AGENTS
LOOP
myobj.extend;
myobj(myobj.count) := CUR.ACCOUNT_NUM ;
END LOOP;
return myobj;
end;
/
Yes in subquery I have just called column_value with alias as acct_num
Last edited by Bonker; 06-16-2005 at 04:45 AM.
Experience is a hard teacher because she gives the test first, the lesson afterwards.
-
Sorry see_one I coudn't help you out on this bit.
Hi bonker,
can you expalin this one
myobj.extend;
myobj(myobj.count) := CUR.DRIVER_ACCOUNT_NUMBER;
There are three kinds of lies: Lies, damned lies, and benchmarks...
Unix is user friendly. It's just very particular about who it's friends are.
Oracle DBA
-
Hi simply_dba,
The myobj.extend allocates space to this table type and then assigns value with myobj(myobj.count) := <>
i.e. assign value in the new space created and thus assigns all the value in cursor as records in this table.
this could have also done by using bulk collect i.e.
select * bulk collect into myobj from
( select driver_account_number
from driver
where contract_date < sysdate
and contract_expires > sysdate
Hope it explains.
Experience is a hard teacher because she gives the test first, the lesson afterwards.
-
Hi simply_dba,
The myobj.extend allocates space to this table type and then assigns value with myobj(myobj.count) := <>
i.e. assign value in the new space created and thus assigns all the value in cursor as records in this table.
this could have also done by using bulk collect i.e.
create or replace function driv_func return driver_object
as
myobj driver_object := driver_object();
begin
select * bulk collect into myobj from
( select driver_account_number
from driver
where contract_date < sysdate
and contract_expires > sysdate
union all
select account_num
from f01names2
where (account_num between 1 and 1900 OR account_num between
4000 and 6700)
);
return myobj;
end;
/
Hope it explains.
Experience is a hard teacher because she gives the test first, the lesson afterwards.
-
YES! It works like a charm, Bonker. I really appreciate you helping me with this thing. I learned this not for the this report, but for my future projects. I work for a small company and I have to struggle for things like this on my own and to keep learning things for my career's sake. I hope this has helped other members here as well. I want to thank simply_dba and, of course, Bonker. I'm really excited. See you next time.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|