Home » Developer & Programmer » Forms » Looping is not working (Oracle9i, Developer6i, WinXp)
Looping is not working [message #396203] Sat, 04 April 2009 23:24 Go to next message
mahatab
Messages: 98
Registered: January 2008
Location: Dhaka
Member

Hi,
I have written down the following code, where the multiple rows selected according to the conditions. When i am trying to insert those selected row into another table problem arise, its not taking the next value rather taking only the first selected value. Here is the code, please try to help me, if you can:
	begin
		
first_record;
   loop	
    	
SELECT ALL DEALER_CONTRIBUTION.DISTRIBUTOR_CODE,PRODUCT_TARGET1.YEAR_NO,PRODUCT_TARGET1.MONTH_ID,
PRODUCT_TARGET1.PRODUCT_CODE,
((DEALER_CONTRIBUTION.CONTRIBUTION/100)*( PRODUCT_TARGET1.TARGET_QTY))TARGET 
INTO :dealer_target_tbl2.DISTRIBUTOR_CODE,:dealer_target_tbl2.TARGET_YEAR,
:dealer_target_tbl2.MONTH_ID,:dealer_target_tbl2.PRODUCT_CODE,
:dealer_target_tbl2.TARGETED_QTY_MONTHLY
FROM DEALER_CONTRIBUTION,PRODUCT_TARGET1 
WHERE DEALER_CONTRIBUTION.PRODUCT_CODE=PRODUCT_TARGET1.PRODUCT_CODE 
AND DEALER_CONTRIBUTION.YEAR_NO=PRODUCT_TARGET1.YEAR_NO; 
go_block('dealer_target_tbl2');
execute_query();

INSERT INTO DEALER_TARGET_TBL2(DISTRIBUTOR_CODE,        
TARGET_YEAR,            
MONTH_ID,               
PRODUCT_CODE,           
TARGETED_QTY_MONTHLY)
VALUES(:dealer_target_tbl2.DISTRIBUTOR_CODE,:dealer_target_tbl2.TARGET_YEAR,
:dealer_target_tbl2.MONTH_ID,:dealer_target_tbl2.PRODUCT_CODE,
:dealer_target_tbl2.TARGETED_QTY_MONTHLY);

exit when :system.last_record = 'TRUE';
	execute_query();	
	next_record;
  end loop;

  commit;
END;

[Updated on: Sun, 05 April 2009 00:09] by Moderator

Report message to a moderator

Re: Looping is not working [message #396205 is a reply to message #396203] Sat, 04 April 2009 23:35 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>(Oracle9i, Developer6i, WinXp)
All above are obsoleted & no longer supported.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Please, please, please Read & Follow Posting Guidelines above.
Go to the section labeled "Practice" & do as directed.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: Looping is not working [message #396209 is a reply to message #396203] Sun, 05 April 2009 00:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you really write your code like this I understand you can't see if there is an error.
Format your code, indent it, use a code formatter (for instance http://www.orafaq.com/utilities/sqlformatter.htm).

Regards
Michel
Re: Looping is not working [message #396344 is a reply to message #396203] Mon, 06 April 2009 04:14 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
why on earth have you got an execute_query in that loop?
Re: Looping is not working [message #396349 is a reply to message #396203] Mon, 06 April 2009 04:41 Go to previous messageGo to next message
mahatab
Messages: 98
Registered: January 2008
Location: Dhaka
Member

This has been done to show the resulted data into the data block
Re: Looping is not working [message #396355 is a reply to message #396203] Mon, 06 April 2009 05:01 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Don't you think it'd be more sensible to put it after the loop?
Re: Looping is not working [message #396361 is a reply to message #396203] Mon, 06 April 2009 05:20 Go to previous messageGo to next message
dushnkenjoy
Messages: 3
Registered: April 2009
Location: India
Junior Member

You are just populating values into the fields on the form and after that you are using execute query..
If you want to populate some values into some item then use create_record before assigning the values after that use commit
After doing this If you do execute query then only data will be populated into the form from the backend.
Re: Looping is not working [message #396363 is a reply to message #396203] Mon, 06 April 2009 05:30 Go to previous messageGo to next message
mahatab
Messages: 98
Registered: January 2008
Location: Dhaka
Member

Yes later on i have done the same as you suggested. But the result is zero. However let me demonstrate the whole idea one more time:

I have got two table from where according to the condition i have applied it is expected that i will get more than one row. so i wanted to have such a condition where every time i will get one row according to the condition will be stored in the another table and this process will continue until unless the condition is false.

Quote:

SQL> select * from dealer_contribution;

DIST CONTRIBUTION PROD PRODUCT_NAME INV_RATE PACK_SIZE YEAR_NO
---- ------------ ---- -------- --------- --------- ------
1 12 D01 DANISH BIG BITE 222.22 24 2009
2 15 D02 DANISH PIZZA 188.89 24 2009
2 14 D01 DANISH BIG BITE 222.22 24 2009


This table is set earlier. Now when input is stored in the Product_target1 table:
Quote:

SQL> select * from product_target1;

PROD YEAR_NO MONTH_ID TARGET_QTY
---- --------- --------- ----------
D01 2009 4 100


Now according to my condition i will get the following rows which i would like to stored in the another table until the condition is false.

Quote:

DIS COD NAME YEAR MONTH_ID P.CODE P.NAME
1 Md. Kamran Mollik 2009 4 D01 DANISH BIG BITE
2 Mohammad Aminul Ehsan 2009 4 D01 DANISH BIG BITE



I have got the result in the query but when i am trying to stored them suing the loop don't know why i have failed every time. Now please provide me a sample code so that i can work on that.

Regards,
Mahatab Masud
Re: Looping is not working [message #396493 is a reply to message #396203] Mon, 06 April 2009 23:43 Go to previous messageGo to next message
mahatab
Messages: 98
Registered: January 2008
Location: Dhaka
Member

Hi,
At last i have successfully solved the problem i have faced. I am really grateful to those who have shown their commitment to solve out my problem. Here is the complete code for those who may have same kind of problem in future.

DECLARE
cursor cur_target is
select DEALER_CONTRIBUTION.DISTRIBUTOR_CODE,
 DISTRIBUTOR_INFO_2.DISTRIBUTOR_NAME,
 PRODUCT_TARGET1.YEAR_NO,
 PRODUCT_TARGET1.MONTH_ID,
 DEALER_CONTRIBUTION.PRODUCT_CODE,
 DEALER_CONTRIBUTION.PRODUCT_NAME,
 ((DEALER_CONTRIBUTION.CONTRIBUTION/100)*( PRODUCT_TARGET1.TARGET_QTY))TARGETED_QTY_MONTHLY,
 (((DEALER_CONTRIBUTION.CONTRIBUTION/100)*( PRODUCT_TARGET1.TARGET_QTY))*(DEALER_CONTRIBUTION.INV_RATE))TARGETED_AMT_MONTHLY,
 DEALER_CONTRIBUTION.INV_RATE
FROM DEALER_CONTRIBUTION,PRODUCT_TARGET1,DISTRIBUTOR_INFO_2
	WHERE DEALER_CONTRIBUTION.PRODUCT_CODE=PRODUCT_TARGET1.PRODUCT_CODE AND DEALER_CONTRIBUTION.YEAR_NO=PRODUCT_TARGET1.YEAR_NO AND
	DISTRIBUTOR_INFO_2.DISTRIBUTOR_CODE=DEALER_CONTRIBUTION.DISTRIBUTOR_CODE;
	DISTRIBUTOR_CODE VARCHAR2(20);
	DISTRIBUTOR_NAME VARCHAR2(50);
	TARGET_YEAR NUMBER(10);
	MONTH_ID NUMBER(10);
	PRODUCT_CODE VARCHAR2(10);
	PRODUCT_NAME VARCHAR2(50);
	TARGETED_QTY_MONTHLY NUMBER(10);
	TARGETED_AMT_MONTHLY NUMBER(10);
	INV_RATE  NUMBER(10,2);
begin
open cur_target;
 loop
FETCH CUR_TARGET INTO DISTRIBUTOR_CODE, 
DISTRIBUTOR_NAME, 
TARGET_YEAR, 
MONTH_ID, 
PRODUCT_CODE, 
PRODUCT_NAME, 
TARGETED_QTY_MONTHLY, 
TARGETED_AMT_MONTHLY, 
INV_RATE;
exit when cur_target%notfound;
				INSERT INTO DEALER_TARGET_TBL2(DISTRIBUTOR_CODE,
				DISTRIBUTOR_NAME,
				TARGET_YEAR,
				MONTH_ID,
				PRODUCT_CODE,
				PRODUCT_NAME,
				TARGETED_QTY_MONTHLY,
				TARGETED_AMT_MONTHLY,
				INV_RATE)
					VALUES(DISTRIBUTOR_CODE,
					DISTRIBUTOR_NAME,
					TARGET_YEAR,
					MONTH_ID,
					PRODUCT_CODE,
					PRODUCT_NAME,
					TARGETED_QTY_MONTHLY,
					TARGETED_AMT_MONTHLY,
					INV_RATE);
					COMMIT;
NEXT_RECORD;
		--	exit when cur_target%notfound;
 end loop;
close cur_target;
		end;


Sorry i really can't do the formatting in the post, as my code was in format but when post in the editor box the formatting no longer working.
Re: Looping is not working [message #396572 is a reply to message #396203] Tue, 07 April 2009 04:26 Go to previous message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Couple of pointers:
1) your code doesn't appear to reference any datablock items so the next_record appears to be unecessary.
2) If you're looping over a cursor it's easier to use a for loop rather than a simple loop.
3) commits in loops is generally not a good idea - do it once after the loop.
4) Whole thing could probably be accomplished with a single insert/select.
5) code is a lot more readable if you put blank lines between statements.
Previous Topic: Paradox File (.DB) to Oracle
Next Topic: when-validate-item giving strange behavior
Goto Forum:
  


Current Time: Sat Dec 10 16:34:17 CST 2016

Total time taken to generate the page: 0.09284 seconds