Convert select query to a cursor and that has to fetch first record [message #660963] |
Thu, 02 March 2017 13:37 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/638b376d54f88b93d0d0ddd4424968e5?s=64&d=mm&r=g) |
msyogi
Messages: 16 Registered: May 2016 Location: Hyderabad
|
Junior Member |
|
|
Below is the select statament. I want to Convert select query to a cursor and that has to fetch first record. Please help
BEGIN
SELECT nvl(a.late_payment,'S')
INTO lv_late_payment
FROM Diary_tab a, Prem_Bill_BR c
WHERE a.company_cod = c.company_cod
AND a.num_policy = c.num_policy
AND a.cod_type = 'N'
AND a.num_seq IN (SELECT MAX(b.num_seq)
FROM Diary_tab b
WHERE b.company_cod = a.company_cod
AND b.cod_type = a.cod_type
AND b.num_policy = a.num_policy
)
ORDER BY a.fec_ent DESC, a.fec_upd DESC;
EXCEPTION
WHEN OTHERS THEN
lv_late_payment:='S';
END;
*BlackSwan added {code} tags. Please do so yourself in the future.
How to use {code} tags and make your code easier to read
[Updated on: Thu, 02 March 2017 13:42] by Moderator Report message to a moderator
|
|
|
Re: Convert select query to a cursor and that has to fetch first record [message #660964 is a reply to message #660963] |
Thu, 02 March 2017 14:13 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
declare
lv_late_payment varchar2(20);
begin
for idx in (SELECT nvl(a.late_payment,'S') late_payment
FROM Diary_tab a, Prem_Bill_BR c
WHERE a.company_cod = c.company_cod
AND a.num_policy = c.num_policy
AND a.cod_type = 'N'
AND a.num_seq IN (SELECT MAX(b.num_seq)
FROM Diary_tab b
WHERE b.company_cod = a.company_cod
AND b.cod_type = a.cod_type
AND b.num_policy = a.num_policy
)
ORDER BY a.fec_ent DESC, a.fec_upd DESC) loop
lv_late_payment := idx.last_payment;
end loop;
end;
/
[Updated on: Thu, 02 March 2017 14:14] Report message to a moderator
|
|
|
|
|