Home » SQL & PL/SQL » SQL & PL/SQL » Comparing the resulset of cursor with table column data (Oracle 9.2.0.3)
Comparing the resulset of cursor with table column data [message #404648] Fri, 22 May 2009 10:56 Go to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member

  for rec in ( select  department_id, department_name from table1)
  loop
  /*if rec.department_id falls in or exists in  list of geographical area ( area_cd from some table which is a result of 
  two or more table))*/ 
  then  
  <perform some action using rec.department_id >;
  end if;
  end loop;  


What is the best approach to write for pseudo code below.

if rec.department_id falls in or exists in list of geographical area ( area_cd from some table which is a result of
two or more table))

Many thanks in advance

[Updated on: Fri, 22 May 2009 11:14] by Moderator

Report message to a moderator

Re: Comparing the resulset of cursor with table column data [message #404649 is a reply to message #404648] Fri, 22 May 2009 11:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
What is the best approach to write for pseudo code below.

if rec.department_id falls in or exists in list of geographical area ( area_cd from some table which is a result of
two or more table))

Include it in the loop query.

Regards
Michel
Re: Comparing the resulset of cursor with table column data [message #404651 is a reply to message #404649] Fri, 22 May 2009 11:22 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Sir, Is it not possible using function? because I need to check if rec.department_id exist in another resul set (select area_cde from a, b where ...)
Re: Comparing the resulset of cursor with table column data [message #404656 is a reply to message #404651] Fri, 22 May 2009 11:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
prachij593 wrote on Fri, 22 May 2009 18:22
Sir, Is it not possible using function? because I need to check if rec.department_id exist in another resul set (select area_cde from a, b where ...)

Yes it is possible but you asked for the best approach.

Regards
Michel

Re: Comparing the resulset of cursor with table column data [message #404657 is a reply to message #404656] Fri, 22 May 2009 11:50 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Thanks Sir.
There are multiple conditions. eg,

for rec in ( select  department_id, department_name from table1)
  loop

   if rec.department_id = '999' then ( this is not compared with resulset)
   < action A>
   end if;

   if rec.department_id = '005' then
   < action A>
   end if;

     
 
  /*if rec.department_id falls in or exists in  list of geographical area ( area_cd from some table which is a result of 
  two or more table))*/ 
  then  
  <perform  action A using rec.department_id >;
  end if;
  end loop;  


[Updated on: Fri, 22 May 2009 11:51]

Report message to a moderator

Re: Comparing the resulset of cursor with table column data [message #404660 is a reply to message #404657] Fri, 22 May 2009 12:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a complete example and not an oversimplified one this will prevent us from wasting our time with inappropriate answer.

Regards
Michel
Re: Comparing the resulset of cursor with table column data [message #404681 is a reply to message #404660] Fri, 22 May 2009 15:04 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Michel Sir, The above was the completed one.
Re: Comparing the resulset of cursor with table column data [message #404733 is a reply to message #404681] Sat, 23 May 2009 01:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You only have 3 solutions:
- use 1 loop and fetch all and use if to separate actions depending on case
- use many loops and use one action per loop that fetches specific rows
- mix up both solutions

Choose yours.

Regards
Michel
Re: Comparing the resulset of cursor with table column data [message #404865 is a reply to message #404733] Mon, 25 May 2009 01:46 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Cant we do something like

if rec.department_id = ANY (select a.area_cd from ... where ...) 


something like this way?
Re: Comparing the resulset of cursor with table column data [message #404873 is a reply to message #404865] Mon, 25 May 2009 02:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, there is not.
You have to write it in other way like "select 1 from dual where rec.department_id = ANY (select a.area_cd from ... where ...)" or "select null from ... where ... and a.area_cd = rec.department_id". If the query does not raise no_data_found then it is true.

Regards
Michel

[Updated on: Mon, 25 May 2009 02:13]

Report message to a moderator

Re: Comparing the resulset of cursor with table column data [message #404902 is a reply to message #404873] Mon, 25 May 2009 06:15 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Thanks Sir,

Can we do something to check if rec.department_id exisits in the
resulset like this way



initialize v_flag as null
select  1 into v_flag from dual where rec.department_id = ANY (select a.area_cd from ... where ...) 
now if v_flag=1 then...
<action>



like this way?


[Updated on: Mon, 25 May 2009 06:48]

Report message to a moderator

Re: Comparing the resulset of cursor with table column data [message #404908 is a reply to message #404902] Mon, 25 May 2009 07:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't have to check the value of flag, if the query returns then it is true, if it raises a no_data_found exception then it is false.
So your block is like:
begin 
 select 1 into flag from dual ...
 <do something>
exception when no_data_found then null;
end;

Note that the most efficient way to write the query depends on many things like your tables, your conditions, your indexes and so on. The ones I gave were just examples.

Regards
Michel
Re: Comparing the resulset of cursor with table column data [message #404911 is a reply to message #404908] Mon, 25 May 2009 07:23 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Thank you very much.


Actually I wanted to check if rec.dept_id value exists in a resulset and
if found ( flag =1 ) then
call some procedure say a1 with some values
else
call procedure A1 passing with different value
end if;
Re: Comparing the resulset of cursor with table column data [message #404914 is a reply to message #404911] Mon, 25 May 2009 07:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Then replace "null;" by "call procedure A1 passing with different value".

Regards
Michel
Re: Comparing the resulset of cursor with table column data [message #404923 is a reply to message #404914] Mon, 25 May 2009 08:29 Go to previous message
prachij593
Messages: 266
Registered: May 2009
Senior Member
many thanks!!!!
Previous Topic: Executing subprocedure
Next Topic: Error: ORA-00933 SQL command not properly ended (merged 3)
Goto Forum:
  


Current Time: Sat Dec 03 10:18:12 CST 2016

Total time taken to generate the page: 0.18683 seconds