Do you know why the DECIMAL value of my user variable is truncated?

I made a MySQL function which almost does what I want, but not quite.

I make a user variable:

SELECT @start := price FROM prices_up_down WHERE id = 1;

Then UPDATE:

UPDATE prices_up_down SET signal_ = comparePrice(price), start_value = @start;

The variable @start should have 2 decimal places , but I just end up with .00

I don’t know why the decimal places are truncated. Anyone have an idea?

For example, row id = 4 signal_ should be 0, but because @start gets truncated, I get a -1
How can I stop @start being truncated??

This is the output

id price signal_ start_value
1 100.86 0 100.86
2 99.19 -1 99
3 100.6 1 101
4 100.43 -1 100
5 100.6 1 101
6 100.57 0 101
7 101.83 1 102
![image 216x137](upload://vAj4ZKFVq2VFxjkhX4JyIq7LzMv.png)

Here is the function, my first MySQL function. The function should check the column price. If there is a change, up or down, of 1 or more, the column signal_ should be modified to 1 if price increased, -1 if price decreased, 0 if the change was less than 1.

I am getting a warning:

Warning: #1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: ‘SET variable=expression, …’, or ‘SELECT expression(s) INTO variables(s)’.

DELIMITER //

CREATE FUNCTION comparePrice(p DECIMAL)
RETURNS INT DETERMINISTIC
BEGIN
DECLARE signalnum INT;
# @start is the first value in row 1 when we start
# if p > @start by 1 or more price has gone up 
IF  p - @start >= 1  THEN SET signalnum := 1, @start := p;
# if @start > p by 1 or more price has gone down
ELSEIF @start - p >= 1 THEN SET signalnum := -1, @start := p;
# if the change is < 1, signalnum = 0
ELSE SET signalnum := 0;
END IF;
RETURN signalnum;
END; //

DELIMITER ;
Sponsor our Newsletter | Privacy Policy | Terms of Service