6 replies to this topic

[njr1489]

I have a table called pricing. It has 3 columns: ITEM_CODE, ITEM_DESC, and PRICE. I'm supposed to use implicit cursors to obtain the information, however in the second step I have to increase the price of an item by 6%. I try and I get the output statements without an answer to the new price. Is this because the multiplication causes a loss of precision? Here is my code below.

DECLARE

	increase NUMBER(1,2) := .06;

	current_item_desc pricing.ITEM_DESC%TYPE;

	current_price pricing.PRICE%TYPE;

	new_price NUMBER(2,2) := current_price + current_price * increase;	

BEGIN

	SELECT ITEM_DESC, PRICE

	INTO current_item_desc, current_price

	FROM pricing

	WHERE ITEM_CODE = 1;

	DBMS_OUTPUT.PUT_LINE('The item description is: ' || current_item_desc);

	DBMS_OUTPUT.PUT_LINE('The price is: ' || new_price);

END;

/

[Orjan]

usually, to add 6% is easier with old * 1.06 instead of old + old * 0.06. it makes the calculation safer in my world.

on the other handis new_price actually recalculated for each row? don't you have to do that calculation each row? I don't know PL/SQL, but I'd do

DBMS_OUTPUT.PUT_LINE('The price is: ' || new_price * increase);

based on changing increase to 1.06 instead of 0.06

[njr1489]

Either way I tried it, nothing gets calculated. The output will display the strings, but now new_price. Here is my current code:

DECLARE
	increase NUMBER(2) := 1.06;
	current_item_desc pricing.ITEM_DESC%TYPE;
	current_price pricing.PRICE%TYPE;
	new_price NUMBER(2) := current_price * increase;	
BEGIN
	SELECT ITEM_DESC, PRICE
	INTO current_item_desc, current_price
	FROM pricing
	WHERE ITEM_CODE = 1;
	DBMS_OUTPUT.PUT_LINE('The item description is: ' || current_item_desc);
	DBMS_OUTPUT.PUT_LINE('The price is: ' || new_price);
END;
/

Edited by njr1489, 15 May 2009 - 06:37 PM.
Added picture

[Orjan]

You didn't read what I said?

"new_price" get value 0 when you define it, as current_price are nil, nada, null, nothing, not defined at the time. you need to do that calculation inside the loop. understand? comprende?

yes i can see now I made a mistake in my code snippet.

DBMS_OUTPUT.PUT_LINE('The price is: ' || current_price * increase);

shall do the trick

[njr1489]

Whoops, I copied the wrong code when making the last post. Anyway, I got it, thanks for the help.

DECLARE
	increase NUMBER := 1.06;
	current_item_desc pricing.ITEM_DESC%TYPE;
	current_price pricing.PRICE%TYPE;
	new_price NUMBER := current_price * increase;	
BEGIN
	SELECT ITEM_DESC, PRICE
	INTO current_item_desc, current_price
	FROM pricing
	WHERE ITEM_CODE = 1;
	DBMS_OUTPUT.PUT_LINE('The item description is: ' || current_item_desc);
	DBMS_OUTPUT.PUT_LINE('The price is: ' || current_price * increase);
END;
/

[Orjan]

did this solve the problem?

[njr1489]

Yes, thanks. I did make other changes for it to actually update on the table itself however.