Convert select query to a cursor and that has to fetch first record [message #660963] |
Thu, 02 March 2017 13:37 |
|
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 |
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
|
|
|
|
|