Home » SQL & PL/SQL » SQL & PL/SQL » Convert select query to a cursor and that has to fetch first record (Oracle 11g)
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
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 messageGo to next message
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

Re: Convert select query to a cursor and that has to fetch first record [message #660966 is a reply to message #660963] Thu, 02 March 2017 14:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
declare 
  lv_late_paymentvarchar2(X);
begin
  select * INTO lv_late_payment from (
  SELECT nvl(a.late_payment,'S')
           
           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
   where rownum = 1;
end;
/

Read WHEN OTHERS and don't do it, this is a bug in your code.

Re: Convert select query to a cursor and that has to fetch first record [message #660967 is a reply to message #660963] Thu, 02 March 2017 14:21 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And review your previous topics and feedback and thank people something you only did for your first topic, why not in the subsequent ones?

Do it now.

Previous Topic: Using debug in EBS r12 database with SQL Developer
Next Topic: Bitmap index for nullable timestamp column ?
Goto Forum:
  


Current Time: Fri Mar 29 01:54:33 CDT 2024