Home » SQL & PL/SQL » SQL & PL/SQL » how to use a cursor so as it selects value from another cursor
how to use a cursor so as it selects value from another cursor [message #235512] Mon, 07 May 2007 00:30 Go to next message
cutsmartprem
Messages: 62
Registered: November 2006
Member
Hi All,
This is the example of what i need.

declare
curdate date;
cursor c1 is
select
receipt_no,
receipt_date,
receipt_curr,
receipt_amt
from
receipts;
begin
select sysdat into curdate from dual;
commit;
for c in c1
loop
if(trunc(curdate,'Q') = trunc(c.receipt_date,'Q')) then
book_rate = select bk_rt from book where book_dt = trunc(c.receipt_date'Q');
else
book_rate = select bk_rt from book where book_dt = add_months(trunc(curdate,'Q')-3);
end if;
end loop;

end;

In the above pl/sql code for each receipt a book rate is calculated. I want to again select the receipts with the book rate in another cursor and group the amount, currency and quarter wise. Thanks in advance.
Re: how to use a cursor so as it selects value from another cursor [message #235515 is a reply to message #235512] Mon, 07 May 2007 00:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why not directly:
select receipt_no, receipt_date, receipt_curr, receipt_amt, bk_rt 
from receipts r, book b
where b. book_dt = decode(trunc(c.receipt_date,'Q'),
                          trunc(curdate,'Q'), trunc(c.receipt_date,'Q'),
                          add_months(trunc(curdate,'Q'),-3))
;

Regards
Michel
Re: how to use a cursor so as it selects value from another cursor [message #235516 is a reply to message #235515] Mon, 07 May 2007 00:51 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
Brilliant!
You are almost as good as the "master" in SQL
Re: how to use a cursor so as it selects value from another cursor [message #235519 is a reply to message #235516] Mon, 07 May 2007 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But I AM the master. Laughing

Regards
Michel
Re: how to use a cursor so as it selects value from another cursor [message #235522 is a reply to message #235519] Mon, 07 May 2007 01:01 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
For me the master is a guy named Thomas Kyte, but I think I have found another one here!!

[Updated on: Mon, 07 May 2007 01:12]

Report message to a moderator

Re: how to use a cursor so as it selects value from another cursor [message #235523 is a reply to message #235522] Mon, 07 May 2007 01:04 Go to previous messageGo to next message
cutsmartprem
Messages: 62
Registered: November 2006
Member
that is just an example of what i need. There are many more values and calculations to be done.I have given the crisp idea of what i need.
Also from the rows retrieved i have to sum the amount fields and group by the currency and trunc(invoice_date,'Q')

I have also written a single select code which is very long and its difficult for others to understand. Request you to kindly help me out.
Re: how to use a cursor so as it selects value from another cursor [message #235526 is a reply to message #235523] Mon, 07 May 2007 01:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A SQL query is NEVER slower than a PL/SQL block doing the same thing unless you badly wrote the query or fooled the optimizer.

Request you to kindly format your post.

Regards
Michel
Re: how to use a cursor so as it selects value from another cursor [message #235529 is a reply to message #235526] Mon, 07 May 2007 01:26 Go to previous messageGo to next message
cutsmartprem
Messages: 62
Registered: November 2006
Member
i know it is fast and the query is also working fine,but it will be difficult to maintain the code for someone else...
Re: how to use a cursor so as it selects value from another cursor [message #235536 is a reply to message #235526] Mon, 07 May 2007 01:40 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Michel Cadot wrote on Mon, 07 May 2007 08:16
A SQL query is NEVER slower than a PL/SQL block doing the same thing unless you badly wrote the query or fooled the optimizer.
Hmmm.. Michel, I have seen a PL/SQL block outperform a query. Here's an interesting thread.

MHE
Re: how to use a cursor so as it selects value from another cursor [message #235540 is a reply to message #235536] Mon, 07 May 2007 01:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is a very special case design to warp SQL to make it work for something it can't normally do. Moreover the query with MAX is not the fastest way to work with it.
In this case, the SQL query is just an exercise not really a solution. Of course, custom aggregate function is the solution in this case as it can have benefit of parallelism and optimizer optimisations.

I had fun to build exercise to calculate factorial on Fibonacci numbers... in SQL but they are not practical queries and are of course slower than any program (although...).

Regards
Michel
Re: how to use a cursor so as it selects value from another cursor [message #235543 is a reply to message #235540] Mon, 07 May 2007 02:13 Go to previous messageGo to next message
cutsmartprem
Messages: 62
Registered: November 2006
Member
yes what you mean is correct. But my single query is about 450 lines and it will be difficult for others to maintain in the future.
Re: how to use a cursor so as it selects value from another cursor [message #235544 is a reply to message #235543] Mon, 07 May 2007 02:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A query, like a program, can be pretty written with comments.
Moreover, it should have specification and each part has its justification.
If its design is clear and straight, it is as easy to maintain as any other program.

Regards
Michel
Re: how to use a cursor so as it selects value from another cursor [message #235550 is a reply to message #235544] Mon, 07 May 2007 02:28 Go to previous messageGo to next message
cutsmartprem
Messages: 62
Registered: November 2006
Member
ok.... im convinced with your reply and thanks for it... Let me know whether i can use a cursor for it...
Re: how to use a cursor so as it selects value from another cursor [message #235577 is a reply to message #235550] Mon, 07 May 2007 05:04 Go to previous message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you insist and want it, there is no reason you can't.

Regards
Michel
Previous Topic: Lock row and notify to other computer
Next Topic: combination of count and distinct query
Goto Forum:
  


Current Time: Wed Dec 07 14:27:44 CST 2016

Total time taken to generate the page: 0.09245 seconds