Hi I have bene trying to complete this task for about 2 days now and i need help. I want to show my function in tabular format. Any code samples would be very much appreciated. The problem is i want a pass in a parameter into an oracle function such as '+' or a '' depending on these values i can calculate fields. if '+' then (SUM(A.COST_MSO)) + (SUM(A.COST_MSO)) * B.VARIANCE_PER/100; if '' then (SUM(A.COST_MSO)) + (SUM(A.COST_MSO)) * B.VARIANCE_PER/100; I am using a cursor where i have a select state that joins to tables (table a and table b) for example (i have written this if there is a easier way to do this any help would be appreciated) CREATE OR REPLACE FUNCTION itp_v (p_spccode IN VARCHAR2,p_dealership IN VARCHAR2,p_manufacturer IN VARCHAR2,p_varaiance IN VARCHAR2) RETURN varaiant IS TYPE ref0 IS REF CURSOR; cur0 ref0; v_value number; out_rec varaiant_values := varaiant_values(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); BEGIN OPEN cur0 FOR 'SELECT replace(replace(A.part_number,'',''),'/','') as PartNumber, A.DEALERSHIP, A.SOURCE, A.RETAIL_PRICE, ' 'A.QTY_ON_HAND, B.SOURCE, B.VARIANCE_SIGN, A.AVERAGE_COST, a.COST_MSO, A.MANUFACTURER, ' 'B.BASE_PRICE, B.VARIANCE_PER, A.PART_DESCRIPTION, 0 as VARIANCE_CALC ' 'FROM RWG_STOCK A, RWG_PARTS_SPC B ' 'WHERE A.SOURCE=B.SOURCE ' 'AND B.SPC_CODE = :1 ' 'AND A.DEALERSHIP=B.DEALERSHIP ' 'AND A.DEALERSHIP= :2 ' 'AND B.MANUFACTURER IN ( :3 ) ' 'AND B.MANUFACTURER=A.MANUFACTURER ' 'AND B.VARIANCE_SIGN = :4 ' 'AND ( ( ( A.QTY_ON_HAND NOT IN (( 0 )) ' 'OR A.QTY_BACK_ORDER NOT IN (( 0 )) ' 'OR A.QTY_ON_ORDER NOT IN (( 0 )) ) ) ) ' 'GROUP BY ' 'a.part_number, ' 'A.DEALERSHIP, ' 'A.RETAIL_PRICE, ' 'A.QTY_ON_HAND, ' 'A.SOURCE, ' 'B.SOURCE, ' 'B.VARIANCE_SIGN, ' 'A.AVERAGE_COST, ' 'A.COST_MSO, ' 'A.MANUFACTURER, ' 'B.BASE_PRICE, ' 'B.VARIANCE_PER, ' 'A.PART_DESCRIPTION ' USING p_spccode,p_dealership, p_manufacturer, p_varaiance; LOOP FETCH cur0 INTO out_rec.PARTNUMBER, out_rec.DEALERSHIP out_rec.SOURCE, out_rec.RETAIL_PRICE out_rec.QTY_ON_DEMAND, out_rec.SOURCE out_rec.VARIANCE_SIGN, out_rec.AVERAGE_COST out_rec.COST_MSO, out_rec.MANUFACTURER out_rec.BASE_PRICE, out_rec.VARIANCE_PER out_rec.PART_DESCRIPTION, out_rec.v_value; EXIT WHEN cur0%NOTFOUND if(p_varaiance == '+') THEN v_value := (SUM(out_rec.COST_MSO))  (SUM(out_rec.COST_MSO)) * out_rec.VARIANCE_PER/100; CLOSE cur0; RETURN; END itp_v; Many Thanks When you post your question, there is a little button with 1's and 0's on it that is for "code." If you highlight a section of text and click it, it preserves your formatting. It would help greatly if you would edit your question and format your code to be more readable.
Feb 28, 2010 at 08:50 PM
KillerDBA
(comments are locked)

If you could provide some of the following information it would help us help you.
SELECT replace(replace(A.part_number,'  ',''),' /','') as PartNumber, A.DEALERSHIP, A.SOURCE, A.RETAIL_PRICE, A.QTY_ON_HAND, B.SOURCE, B.VARIANCE_SIGN, A.AVERAGE_COST, a.COST_MSO, A.MANUFACTURER, B.BASE_PRICE, B.VARIANCE_PER, A.PART_DESCRIPTION, 0 as VARIANCE_CALC FROM RWG_STOCK A, RWG_PARTS_SPC B WHERE A.SOURCE=B.SOURCE AND B.SPC_CODE = :spccode AND A.DEALERSHIP = B.DEALERSHIP AND A.DEALERSHIP = :dealership AND B.MANUFACTURER = :manufacturer AND B.MANUFACTURER=A.MANUFACTURER AND B.VARIANCE_SIGN = :varaiance AND ( A.QTY_ON_HAND 0 OR A.QTY_BACK_ORDER 0 OR A.QTY_ON_ORDER 0 );
(comments are locked)
