Home » SQL & PL/SQL » SQL & PL/SQL » Struck in Loop (merged)
Struck in Loop (merged) [message #427145] Wed, 21 October 2009 05:49 Go to next message
ramr_Sw
Messages: 31
Registered: October 2009
Member
Hi

I have two select statements

select t1.loc from t1,t2 where t1.p1= p2.t2 ----- i

select t3.loc from t3 ---- ii

if the loc exists in (ii) from (i) ,then loop to the next location

if not,need to create a record in the table (t3)

pls let me know which s the best way to use this logic.

thanks in adavance
Re: Help me,Pls [message #427150 is a reply to message #427145] Wed, 21 October 2009 05:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
insert into ...
select ...
where not exist ...

or where ... not in ...

Regards
Michel
Re: Help me,Pls [message #427154 is a reply to message #427150] Wed, 21 October 2009 06:14 Go to previous messageGo to next message
ramr_Sw
Messages: 31
Registered: October 2009
Member

should it be placed in a for loop or Cursor in order to insert almost 600 records?
Re: Help me,Pls [message #427156 is a reply to message #427154] Wed, 21 October 2009 06:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, use a single insert statement.

By the way, 600 is really really tiny tiny.

Regards
Michel
Re: Help me,Pls [message #427162 is a reply to message #427156] Wed, 21 October 2009 06:48 Go to previous messageGo to next message
ramr_Sw
Messages: 31
Registered: October 2009
Member
Pls confirm

But,My requirement says that the inserted record should be unique...the primary key should be incremented by one in every loop increment...
Re: Help me,Pls [message #427163 is a reply to message #427162] Wed, 21 October 2009 06:52 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Please post a test case (Create table and Inset statement)


Create a sequence and then you can use Before Insert Trigger or you can use the sequence inside the loop.

Please provide your code. It will help to understand your requirement well

[Updated on: Wed, 21 October 2009 06:54]

Report message to a moderator

Re: Help me,Pls [message #427168 is a reply to message #427162] Wed, 21 October 2009 07:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ramr_Sw wrote on Wed, 21 October 2009 13:48
Pls confirm

But,My requirement says that the inserted record should be unique...the primary key should be incremented by one in every loop increment...

I don't any of such requirement in your previous posts.
You have the answer from what you post. You can't have an answer for what you don't post.

Now:
Please read OraFAQ Forum Guide
Post a working Test case: create table and insert statements along with the result you want with these data.
Also always post your Oracle version with 4 decimals.

Regards
Michel

[Updated on: Wed, 21 October 2009 07:04]

Report message to a moderator

Re: Help me,Pls [message #427180 is a reply to message #427168] Wed, 21 October 2009 07:40 Go to previous messageGo to next message
ramr_Sw
Messages: 31
Registered: October 2009
Member
Hi Michael

Pls find the requirement

select a.loc from history a,model b
where a.unit = b.unit ----------> 1

pls check below if a record exists for the a.loc identified above,

select loc from profile where model = 'MAGI_SW' -------> 2

if yes, loop to the next loc

if No, create a record in profile such that

Profile:profileid = model:parameterid +1
Profile: unit = history:unit
Profile:loc = history:loc

increment parameterid counter by one after it has been written into profile table



thanks in advance
ramr_sw



Re: Help me,Pls [message #427181 is a reply to message #427145] Wed, 21 October 2009 07:48 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
You might want to think about putting an appropriate subject for your posts. This is at least the second post with the same subject title, and no relation to the other.
Re: Help me,Pls [message #427182 is a reply to message #427180] Wed, 21 October 2009 07:58 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
As has already been asked of you (twice). Please Post A Test Case
Re: Help me,Pls [message #427183 is a reply to message #427180] Wed, 21 October 2009 08:03 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

increment parameterid counter by one after it has been written into profile table.


That requirement definitely has "sequence" written all over it.

Struck in Loop [message #427307 is a reply to message #427145] Wed, 21 October 2009 23:08 Go to previous messageGo to next message
ramr_Sw
Messages: 31
Registered: October 2009
Member
Pls find the requirement,I am using toad 9.0.1.8

select a.loc from history a,model b
where a.unit = b.unit
----------> 1

pls check below if a record exists for the a.loc identified above,
select loc from profile where model = 'MAGI_SW' -------> 2

if yes, loop to the next loc

if No, create a record in profile such that

Profile:profileid = model:parameterid +1
Profile: unit = history:unit
Profile:loc = history:loc

increment parameterid counter by one after it has been written into profile table



here,I am not able to give a check of loc from 1 in 2 in FOR loop.
Pls provide me the idea how to use TWO SELECT statement to get the requirement.

thanks in advance
Re: Struck in Loop [message #427308 is a reply to message #427307] Wed, 21 October 2009 23:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you start a new topic for the same question of your previous one?
Why don't you follow the guide you asked you and post a test case?

Regards
Michel
Re: Struck in Loop [message #427313 is a reply to message #427308] Wed, 21 October 2009 23:57 Go to previous messageGo to next message
ramr_Sw
Messages: 31
Registered: October 2009
Member
Hi Michal
pls find below is my test case
declare 
  v_loc varchar2(50);
  cursor c10 is 
    select a.loc 
    from history a,model b
    where a.unit = b.unit ;
begin
  for x in c10 loop
    select loc into v_loc
    from profile 
    where model = 'MAGI_SW'and loc=x.loc
    if sql%rowcount = 0 then
       insert into profile(id,loc,model) values    (id_seq.nextval,x.loc,'MAGIC_SW');
  end if;
 end loop;
end;

and it is throwing error the following error" No data found in select statement"

pls suggest me the best way to use it.

thanks

[EDITED by LF: applied [code] tags]

[Updated on: Thu, 22 October 2009 00:16] by Moderator

Report message to a moderator

Re: Struck in Loop [message #427316 is a reply to message #427313] Thu, 22 October 2009 00:15 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Either make sure that SELECT LOC INTO V_LOC ... always returns value, or write exception handler unit which will take care of exception(s).

You can do that in several ways; for testing purposes, you can even avoid EXCEPTION section by using an aggregate function. For example: this will return NO_DATA_FOUND
SQL> select 'x' from dept where deptno = 999;

no rows selected
but this will not:
SQL> select max('x') from dept where deptno = 999;

M
-


SQL>

EXCEPTION can be used on two locations: at the end of the whole PL/SQL block (in that case, the very first exception will exit the loop and your procedure will terminate):
DECLARE 
 ...
BEGIN
  FOR x IN c10 LOOP
     ...
  END LOOP;
EXCEPTION
  WHEN NO_DATA_FOUND THEN 
    ...
END;
or within a new BEGIN-END block which should be created in the loop. In that case, loop would continue looping
DECLARE 
 ...
BEGIN
  FOR x IN c10 LOOP
    BEGIN
      ...
    EXCEPTION
      WHEN NO_DATA_FOUND THEN 
       ...
    END;
  END LOOP;
END;

Re: Struck in Loop [message #427321 is a reply to message #427313] Thu, 22 October 2009 00:48 Go to previous message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ramr_Sw wrote on Thu, 22 October 2009 06:57
Hi Michal
pls find below is my test case

This is NOT a test case, a test case is something that EVERYONE can run.
In addition you didn't format your post.
Please do it.

Regards
Michel

Previous Topic: insert versus merge
Next Topic: Update not detect table in Procedure
Goto Forum:
  


Current Time: Mon Dec 05 08:47:30 CST 2016

Total time taken to generate the page: 0.14935 seconds