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;
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
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 ;