x

Need help with Oracle Function to calculate values

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;
PIPE ROW(out_rec);
ELSEIF (p_varaiance == '-') THEN v_value := (SUM(out_rec.COST_MSO)) - (SUM(out_rec.COST_MSO)) * out_rec.VARIANCE_PER/100; PIPE ROW(out_rec); ELSE v_value := 0; PIPE ROW(out_rec); END IF; END LOOP;

CLOSE cur0;

RETURN; END itp_v;

Many Thanks

more ▼

asked Feb 24, 2010 at 07:45 AM in Default

Razwan gravatar image

Razwan
21 1 1 1

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)
10|1200 characters needed characters left

1 answer: sort voted first

If you could provide some of the following information it would help us help you.

  • What version of Oracle is this for?
  • What is supposed to be different about the processing of p_varaiance when it is +, -, or neither. As far as I can tell they are all will return the same results.
  • Since you are using PIPE ROW, why doesn't the header include the keyword PIPELINED?
  • Why are you grouping?
  • What error are you getting? Is it a compile error or a run time error?
  • What in general are you trying to accomplish? What you are doing is about the same as the query below, but I suspect what you are trying to do is at least a bit more complicated.
  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 ); 
more ▼

answered Feb 24, 2010 at 03:42 PM

Leigh Riffel gravatar image

Leigh Riffel
154 8 10 12

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x378
x50
x19

asked: Feb 24, 2010 at 07:45 AM

Seen: 1021 times

Last Updated: Feb 24, 2010 at 07:45 AM