Home » SQL & PL/SQL » SQL & PL/SQL » If exists clause in oracle
If exists clause in oracle [message #656504] Sat, 08 October 2016 16:55 Go to next message
mazeem
Messages: 2
Registered: October 2016
Junior Member
Hi,


I found the following statement in mysql trigger which needs to migrated in oracle ,kindly help me to resolve this problem


if exists (select 1 from stakeholder_extids where stakeh_type='RA' and

case when coalesce(ext_acq_id,'oOo')=coalesce(:new.ext_acq_id,'oOo') then 1 else 0 end=1 and

case when coalesce(ext_merchant_id,'oOo')=coalesce(:new.ext_merchant_id,'oOo') then 1 else 0 end=1 and

case when coalesce(ext_store_id,'oOo')=coalesce(:new.ext_store_id,'oOo') then 1 else 0 end=1 and

case when coalesce(ext_device_id,'oOo')=coalesce(:new.ext_device_id,'oOo') then 1 else 0 end=1

) then

insert into trrans_requests_aabbcc values(1);

end if;


Complete code for trigger is


create or replace TRIGGER stakeholder_extid_dirt_i before insert on stakeholder_extids for each row

Declare v_rec_no Number(19);

begin

gen_rec_no('I','stakeholder_extids',v_rec_no);

insert into stakeholder_extidh (action_no ,stakeholder_id ,extid_srno ,stakeh_type ,ext_acq_id ,ext_merchant_id ,ext_store_id ,ext_device_id ,last_upd_on ,geog_id ,last_upd_dtime ,extid_name ,stkhldr_ext_dbit04_flg ) values(v_rec_no ,:new.stakeholder_id ,:new.extid_srno ,:new.stakeh_type ,:new.ext_acq_id ,:new.ext_merchant_id ,:new.ext_store_id ,:new.ext_device_id ,:new.last_upd_on ,:new.geog_id ,:new.last_upd_dtime ,:new.extid_name ,:new.stkhldr_ext_dbit04_flg );

:new.rec_no:=v_rec_no;

Update central_log set rec_no = v_rec_no where action_no = v_rec_no;

if :new.stakeh_type='RA' then

if exists (select 1 from stakeholder_extids where stakeh_type='RA' and

case when coalesce(ext_acq_id,'oOo')=coalesce(:new.ext_acq_id,'oOo') then 1 else 0 end=1 and

case when coalesce(ext_merchant_id,'oOo')=coalesce(:new.ext_merchant_id,'oOo') then 1 else 0 end=1 and

case when coalesce(ext_store_id,'oOo')=coalesce(:new.ext_store_id,'oOo') then 1 else 0 end=1 and

case when coalesce(ext_device_id,'oOo')=coalesce(:new.ext_device_id,'oOo') then 1 else 0 end=1

) then

insert into trrans_requests_aabbcc values(1);

end if;

end if;

End;
Re: If exists clause in oracle [message #656505 is a reply to message #656504] Sat, 08 October 2016 18:37 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8790
Registered: November 2002
Location: California, USA
Senior Member
create or replace TRIGGER stakeholder_extid_dirt_i 
  before insert on stakeholder_extids 
  for each row
Declare 
  v_rec_no Number(19);
begin
  gen_rec_no('I','stakeholder_extids',v_rec_no);
  insert into stakeholder_extidh 
    (action_no ,stakeholder_id ,extid_srno ,stakeh_type ,ext_acq_id ,ext_merchant_id ,
     ext_store_id ,ext_device_id , last_upd_on ,geog_id ,last_upd_dtime ,extid_name ,
     stkhldr_ext_dbit04_flg ) 
  values
    (v_rec_no ,:new.stakeholder_id ,:new.extid_srno ,:new.stakeh_type ,:new.ext_acq_id ,:new.ext_merchant_id ,
     :new.ext_store_id ,:new.ext_device_id ,:new.last_upd_on ,:new.geog_id ,:new.last_upd_dtime ,:new.extid_name ,
     :new.stkhldr_ext_dbit04_flg );
  :new.rec_no:=v_rec_no;
  Update central_log 
    set   rec_no = v_rec_no 
    where action_no = v_rec_no;
  if :new.stakeh_type='RA' then
--
    for x in 
      (select count(*) cnt 
       from   dual
       where  exists 
              ((select 1 
                from   stakeholder_extids 
                where  stakeh_type='RA' 
                and    nvl(ext_acq_id,'oOo')=nvl(:new.ext_acq_id,'oOo') 
                and    nvl(ext_merchant_id,'oOo')=nvl(:new.ext_merchant_id,'oOo') 
                and    nvl(ext_store_id,'oOo')=nvl(:new.ext_store_id,'oOo') 
                and    nvl(ext_device_id,'oOo')=nvl(:new.ext_device_id,'oOo'))))
    loop
      if (x.cnt = 1) then
        insert into trrans_requests_aabbcc values(1);
      end if;
    end loop; 
--
  end if;
End stakeholder_extid_dirt_i;
/
Re: If exists clause in oracle [message #656506 is a reply to message #656505] Sat, 08 October 2016 20:53 Go to previous messageGo to next message
mazeem
Messages: 2
Registered: October 2016
Junior Member
It worked
Thanks & Best Regards
Re: If exists clause in oracle [message #656508 is a reply to message #656505] Sun, 09 October 2016 01:27 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
    for x in 
      (select count(*) cnt 
       from   dual
       where  exists 
              ((select 1 
                from   stakeholder_extids 
                where  stakeh_type='RA' 
                and    nvl(ext_acq_id,'oOo')=nvl(:new.ext_acq_id,'oOo') 
                and    nvl(ext_merchant_id,'oOo')=nvl(:new.ext_merchant_id,'oOo') 
                and    nvl(ext_store_id,'oOo')=nvl(:new.ext_store_id,'oOo') 
                and    nvl(ext_device_id,'oOo')=nvl(:new.ext_device_id,'oOo'))))
    loop
      if (x.cnt = 1) then
        insert into trrans_requests_aabbcc values(1);
      end if;
    end loop; 
Better is (with dummy_var to be declared in DECLARE section):
begin
  select 1 into dummy_var
  from   stakeholder_extids 
  where  stakeh_type='RA' 
  and    nvl(ext_acq_id,'oOo')=nvl(:new.ext_acq_id,'oOo') 
  and    nvl(ext_merchant_id,'oOo')=nvl(:new.ext_merchant_id,'oOo') 
  and    nvl(ext_store_id,'oOo')=nvl(:new.ext_store_id,'oOo') 
  and    nvl(ext_device_id,'oOo')=nvl(:new.ext_device_id,'oOo');
  insert into trrans_requests_aabbcc values(1);
exception when no data_found then null;
end;

Re: If exists clause in oracle [message #656509 is a reply to message #656508] Sun, 09 October 2016 01:58 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8790
Registered: November 2002
Location: California, USA
Senior Member
Michel Cadot wrote on Sat, 08 October 2016 23:27

Quote:
    for x in 
      (select count(*) cnt 
       from   dual
       where  exists 
              ((select 1 
                from   stakeholder_extids 
                where  stakeh_type='RA' 
                and    nvl(ext_acq_id,'oOo')=nvl(:new.ext_acq_id,'oOo') 
                and    nvl(ext_merchant_id,'oOo')=nvl(:new.ext_merchant_id,'oOo') 
                and    nvl(ext_store_id,'oOo')=nvl(:new.ext_store_id,'oOo') 
                and    nvl(ext_device_id,'oOo')=nvl(:new.ext_device_id,'oOo'))))
    loop
      if (x.cnt = 1) then
        insert into trrans_requests_aabbcc values(1);
      end if;
    end loop; 
Better is (with dummy_var to be declared in DECLARE section):
begin
  select 1 into dummy_var
  from   stakeholder_extids 
  where  stakeh_type='RA' 
  and    nvl(ext_acq_id,'oOo')=nvl(:new.ext_acq_id,'oOo') 
  and    nvl(ext_merchant_id,'oOo')=nvl(:new.ext_merchant_id,'oOo') 
  and    nvl(ext_store_id,'oOo')=nvl(:new.ext_store_id,'oOo') 
  and    nvl(ext_device_id,'oOo')=nvl(:new.ext_device_id,'oOo');
  insert into trrans_requests_aabbcc values(1);
exception when no data_found then null;
end;

"no data_found" should be "no_data_found" and if the select statement returns more than 1 row it will raise "ORA-01422: exact fetch returns more than requested number of rows" error. Another alternative would be to select count(*) into dummy_var and check for >0.



Re: If exists clause in oracle [message #656510 is a reply to message #656509] Sun, 09 October 2016 02:53 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So:
begin
  select 1 into dummy_var
  from   stakeholder_extids 
  where  stakeh_type='RA' 
  and    nvl(ext_acq_id,'oOo')=nvl(:new.ext_acq_id,'oOo') 
  and    nvl(ext_merchant_id,'oOo')=nvl(:new.ext_merchant_id,'oOo') 
  and    nvl(ext_store_id,'oOo')=nvl(:new.ext_store_id,'oOo') 
  and    nvl(ext_device_id,'oOo')=nvl(:new.ext_device_id,'oOo');
  insert into trrans_requests_aabbcc values(1);
exception when no_data_found or too_many_rows then null;
end;
No need to count when the result should be 1.
Counting means execute the statement till the end to have the actual count.
Trapping the exception means stop the execution as soon as you get the second one.

Re: If exists clause in oracle [message #656511 is a reply to message #656504] Sun, 09 October 2016 07:03 Go to previous message
Solomon Yakobson
Messages: 2699
Registered: January 2010
Location: Connecticut, USA
Senior Member
1. No need for v_rec_no and unnecessary assignments between it and :new.rec_no.
2. No need for case statements in select from stakeholder_extids.
3. No need for inner if statement.

create or replace
  trigger stakeholder_extid_dirt_i
    before insert
    on stakeholder_extids
    for each row
    begin
        gen_rec_no(
                   'I',
                   'stakeholder_extids',
                   :new.rec_no
                  );

        insert
          into stakeholder_extidh(
                                  action_no,
                                  stakeholder_id,
                                  extid_srno,
                                  stakeh_type,
                                  ext_acq_id,
                                  ext_merchant_id,
                                  ext_store_id,
                                  ext_device_id,
                                  last_upd_on,
                                  geog_id,
                                  last_upd_dtime,
                                  extid_name,
                                  stkhldr_ext_dbit04_flg
                                 )
          values(
                 :new.rec_no,
                 :new.stakeholder_id,
                 :new.extid_srno,
                 :new.stakeh_type,
                 :new.ext_acq_id,
                 :new.ext_merchant_id,
                 :new.ext_store_id,
                 :new.ext_device_id,
                 :new.last_upd_on,
                 :new.geog_id,
                 :new.last_upd_dtime,
                 :new.extid_name,
                 :new.stkhldr_ext_dbit04_flg
                 );
        update central_log
           set rec_no = :new.rec_no
         where action_no = :new.rec_no;
        if :new.stakeh_type='RA'
          then
            insert
              into trrans_requests_aabbcc
              select  1
                from  stakeholder_extids
                where stakeh_type='RA'
                  and coalesce(ext_acq_id,'oOo') = coalesce(:new.ext_acq_id,'oOo')
                  and coalesce(ext_merchant_id,'oOo') = coalesce(:new.ext_merchant_id,'oOo')
                  and coalesce(ext_store_id,'oOo') = coalesce(:new.ext_store_id,'oOo')
                  and coalesce(ext_device_id,'oOo') = coalesce(:new.ext_device_id,'oOo')
                  and rownum = 1;
        end if;
end;
/


SY.

[Updated on: Sun, 09 October 2016 07:08]

Report message to a moderator

Previous Topic: Declations unknown
Next Topic: compile invalid view
Goto Forum:
  


Current Time: Sun Jan 21 09:30:45 CST 2018

Total time taken to generate the page: 0.04436 seconds