| Can Someone Look At My Procedure Please! [message #80209] |
Fri, 23 August 2002 09:34  |
Tyler
Messages: 123
Registered: January 2002
|
Senior Member |
|
|
Hello All,
I hope that someone can help me... I have a procedure, in looking through it, you will notice the two variables V_TEXT and V_MEASURE, the relationship between these two is one to many, ie. One V_TEXT (feature) can have more than one V_MEASURE (measure/attribute of that feature). My problem is that when the loop starts it gets a V_TEXT and then in that loop, starts looping for measures and in the output, the same V_TEXT is displayed for each of it's measures....
ie. V_TEXT V_MEASURE
- Canoes (14 ft.)
- Canoes (Boating)
- Canoes (3 person seating.)
But I want this...
-Canoes (14ft,Boating,3 person seating)
The procedure follows... any help would be much appreciated...
--the purpose of this script is to obtain all of the features and measures
--related to a product and insert them into the comments column of product_class
DECLARE
v_qualifier varchar2(50);
v_parent NUMBER(38);
v_count number(38);
V_TEXT VARCHAR2(50);
V_APFE_CODE VARCHAR2(10);
V_PARENT_CLAS_CODE NUMBER(38);
V_MEASURE VARCHAR2(50);
V_PRDT_CODE NUMBER(38);
V_PRDT_NAME VARCHAR2(100);
IS_ACCOM VARCHAR2(5);
V_CLAS_CODE NUMBER;
v_temp_clas_code NUMBER(38);
v_counter number:= 0;
V_SUPER_CLAS_CODE number(38);
v_clas_string varchar2(100);
CURSOR CUR_TEXT_APFE IS
SELECT DISTINCT FETX_TEXT,PRFE_APFE_CODE
FROM FEATURE_TEXT,APPROVED_FEATURE,PRODUCT_FEATURE
WHERE FETX_CODE = APFE_FETX_CODE
AND APFE_CODE = PRFE_APFE_CODE
AND PRFE_PRCL_CLAS_CODE = V_SUPER_CLAS_CODE
AND PRFE_PRCL_PRDT_CODE = v_prdt_code;
CURSOR CUR_NO_PARENT IS
SELECT distinct FETX_TEXT,PRFE_APFE_CODE
FROM FEATURE_TEXT,APPROVED_FEATURE,PRODUCT_FEATURE
WHERE FETX_CODE = APFE_FETX_CODE
AND APFE_CODE = PRFE_APFE_CODE
AND PRFE_PRCL_CLAS_CODE = v_clas_code
AND PRFE_PRCL_PRDT_CODE = v_prdt_code;
CURSOR CUR_PRODUCTS IS
SELECT distinct PRDT_NAME,PRDT_CODE FROM PRODUCT,PRODUCT_CLASS
WHERE PRDT_CODE = PRCL_PRDT_CODE;
cursor cur_accom_check is
select distinct prcl_clas_code from product_class where
prcl_prdt_code=v_prdt_code;
cursor cur_measure is
SELECT DISTINCT STDM_UOM_METRIC,PRDM_MINIMUM_VALUE||'-'||PRDM_MAXIMUM_VALUE FROM STANDARD_MEASURE,
APPROVED_FEATURE_MEASURE,PRODUCT_FEATURE_MEASURE
WHERE PRDM_PRFE_PRCL_PRDT_CODE =V_PRDT_CODE AND
PRDM_PRFE_PRCL_CLAS_CODE =V_CLAS_CODE
AND APFM_CODE = PRDM_APFM_CODE
AND STDM_CODE = APFM_FTME_STDM_CODE
AND PRDM_PRFE_APFE_CODE = V_APFE_CODE;
cursor cur_measure_1 is
SELECT DISTINCT STDM_UOM_METRIC,PRDM_MINIMUM_VALUE||'-'||PRDM_MAXIMUM_VALUE FROM STANDARD_MEASURE,
APPROVED_FEATURE_MEASURE,PRODUCT_FEATURE_MEASURE
WHERE PRDM_PRFE_PRCL_PRDT_CODE =V_PRDT_CODE AND
PRDM_PRFE_PRCL_CLAS_CODE =V_SUPER_CLAS_CODE
AND APFM_CODE = PRDM_APFM_CODE
AND STDM_CODE = APFM_FTME_STDM_CODE
AND PRDM_PRFE_APFE_CODE = V_APFE_CODE;
CURSOR CUR_CLAS_PRDT IS
SELECT distinct PRCL_CLAS_CODE FROM PRODUCT_CLASS,PRODUCT
WHERE PRDT_CODE = PRCL_PRDT_CODE AND PRDT_CODE = V_PRDT_CODE;
BEGIN
--START QUERYING PRODUCTS
OPEN CUR_PRODUCTS;
LOOP
-- EXIT WHEN CUR_PRODUCTS%NOTFOUND;
EXIT WHEN V_COUNTER>5;
FETCH CUR_PRODUCTS INTO V_PRDT_NAME,V_PRDT_CODE;
dbms_output.put_line(v_prdt_name);
------------------------------------------------------------------------
--check if product is accom class
OPEN cur_accom_check;
LOOP
exit when cur_accom_check%notfound;
fetch cur_accom_check into v_temp_clas_code;
select clas_string into v_clas_string
from class where clas_code=v_temp_clas_code;
END LOOP;
if v_clas_string like '%ACCOM%' then
is_accom:='Yes';
ELSE
is_accom:='No';
END if;
-----------------------------------------------------------------------
if IS_ACCOM = 'No' THEN --go ahead and get features and measures
OPEN CUR_CLAS_PRDT;
LOOP
FETCH CUR_CLAS_PRDT INTO V_CLAS_CODE;
EXIT WHEN CUR_CLAS_PRDT%NOTFOUND;
--LOOP for only current(lowest sub) class
OPEN CUR_NO_PARENT;
LOOP
FETCH CUR_NO_PARENT INTO V_TEXT,V_APFE_CODE;
EXIT WHEN CUR_NO_PARENT%NOTFOUND;
OPEN CUR_MEASURE;
LOOP
EXIT WHEN CUR_MEASURE%NOTFOUND;
FETCH CUR_MEASURE into V_MEASURE,v_qualifier;
DBMS_OUTPUT.PUT_LINE('CLAS_CODE = '||V_CLAS_CODE||', '||V_PRDT_NAME||','||V_TEXT||'('||v_apfe_code||') '||'('||V_MEASURE||' '||v_qualifier||')');
--clear variables
V_MEASURE:= NULL;
v_qualifier:= NULL;
END LOOP;
CLOSE CUR_MEASURE;
END LOOP;
close CUR_NO_PARENT;
v_measure:=null;
v_text:=null;
--------------start looping for super classes------------------------------
LOOP
--get parents
select clas_clas_code into v_super_clas_code
from class where clas_code=v_clas_code;
exit when v_super_clas_code is null;
--LOOP for only parent (super) classes of current class
OPEN CUR_TEXT_APFE;
LOOP
FETCH CUR_TEXT_APFE INTO V_TEXT,V_APFE_CODE;
EXIT WHEN CUR_TEXT_APFE%NOTFOUND;
OPEN CUR_MEASURE_1;
LOOP
FETCH CUR_MEASURE_1 into V_MEASURE,v_qualifier;
DBMS_OUTPUT.PUT_LINE('SUPER_CLAS_CODE = '||V_SUPER_CLAS_CODE||', '||V_PRDT_NAME||','||V_TEXT||'('||v_apfe_code||') '||'('||'('||V_MEASURE||' '||v_qualifier||')');
--clear variables
V_MEASURE:= NULL;
v_qualifier:= NULL;
EXIT WHEN CUR_MEASURE_1%NOTFOUND;
END LOOP;
CLOSE CUR_MEASURE_1;
END LOOP;
close CUR_TEXT_APFE;
v_clas_code := v_super_clas_code;
END LOOP;--end loop for getting parents
END LOOP;--CUR_CLAS_PRDT
END if;--END if for is_accom
CLOSE CUR_CLAS_PRDT;
close cur_accom_check;
v_counter := v_counter+1;
END LOOP;--END THE PRODUCT QUERY
CLOSE CUR_PRODUCTS;
END;
|
|
|
|
|
|
| Re: Can Someone Look At My Procedure Please! [message #80243 is a reply to message #80209] |
Wed, 28 August 2002 20:17  |
me
Messages: 66
Registered: August 2000
|
Member |
|
|
posting your entire code's not good. next time be concise...
try this....
CURSOR CUR_NO_PARENT IS
SELECT distinct FETX_TEXT,PRFE_APFE_CODE
FROM FEATURE_TEXT,APPROVED_FEATURE,PRODUCT_FEATURE
WHERE FETX_CODE = APFE_FETX_CODE
AND APFE_CODE = PRFE_APFE_CODE
AND PRFE_PRCL_CLAS_CODE = v_clas_code
AND PRFE_PRCL_PRDT_CODE = v_prdt_code;
cursor cur_measure(varMyDummy tablenm.PRFE_APFE_CODE%TYPE) is
SELECT DISTINCT STDM_UOM_METRIC,PRDM_MINIMUM_VALUE||'-'||PRDM_MAXIMUM_VALUE FROM STANDARD_MEASURE,
APPROVED_FEATURE_MEASURE,PRODUCT_FEATURE_MEASURE
WHERE PRDM_PRFE_PRCL_PRDT_CODE = varMyDummy AND
PRDM_PRFE_PRCL_CLAS_CODE =V_CLAS_CODE
AND APFM_CODE = PRDM_APFM_CODE
AND STDM_CODE = APFM_FTME_STDM_CODE
AND PRDM_PRFE_APFE_CODE = V_APFE_CODE;
varMyVar varchar2(1000) := NULL;
then do ...
for cur_rec in CUR_NO_PARENT loop
V_TEXT = cur_rec.FETX_TEXT;
:
:
for cur_rec2 in CUR_MEASURE(cur_rec.PRFE_APFE_CODE) loop
if varMyVar is NULL then
varMyVar := cur_rec2.STDM_UOM_METRIC;
else
varMyVar:=varMyVar ||','|| cur_rec2.STDM_UOM_METRIC;
:
:
end loop;
:
:
end loop;
do the same for the super class. hope this'll be of help. i used your sample as basis ....
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
