Home » Developer & Programmer » Forms » Can Someone Look At My Procedure Please!
Can Someone Look At My Procedure Please! [message #80209] Fri, 23 August 2002 09:34 Go to next message
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 #80234 is a reply to message #80209] Wed, 28 August 2002 02:36 Go to previous messageGo to next message
Oracle Guru
Messages: 3
Registered: May 2001
Junior Member
Too borring proc.
go to hell
Re: Can Someone Look At My Procedure Please! [message #80243 is a reply to message #80209] Wed, 28 August 2002 20:17 Go to previous message
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 ....
Previous Topic: Alert
Next Topic: To find Greatest
Goto Forum:
  


Current Time: Wed Feb 28 07:28:39 CST 2024