Home » Developer & Programmer » Forms » Looping is not working (Oracle9i, Developer6i, WinXp)
| Looping is not working [message #396203] |
Sat, 04 April 2009 23:24  |
|
|
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 #396363 is a reply to message #396203] |
Mon, 06 April 2009 05:30  |
|
|
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 |
|
|
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 |
cookiemonster
Messages: 13963
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.
|
|
|
|
Goto Forum:
Current Time: Mon Feb 03 20:45:26 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
