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

**URL:** <https://www.phphelp.com/t/do-you-know-why-the-decimal-value-of-my-user-variable-is-truncated/35132>\
**Category:** MySQL database\
**Created:** [November 28, 2022, 3:03am UTC](https://www.phphelp.com/t/do-you-know-why-the-decimal-value-of-my-user-variable-is-truncated/35132 "2022-11-28T03:03:18Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Pedroski55](https://www.phphelp.com/letter_avatar_proxy/v4/letter/p/a4c791/32.png) [@Pedroski55](https://www.phphelp.com/u/Pedroski55)\
**Post date:** [November 28, 2022, 3:03am UTC](https://www.phphelp.com/t/do-you-know-why-the-decimal-value-of-my-user-variable-is-truncated/35132/1 "2022-11-28T03:03:18Z")

</div>

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](https://www.phphelp.com/images/transparent.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)’.

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

```

---

<div class="post-metadata">

**Author:** ![mcg](https://www.phphelp.com/user_avatar/www.phphelp.com/mcg/32/4099_2.png) [@mcg](https://www.phphelp.com/u/mcg)\
**Post date:** [March 1, 2023, 5:11pm UTC](https://www.phphelp.com/t/do-you-know-why-the-decimal-value-of-my-user-variable-is-truncated/35132/2 "2023-03-01T17:11:54Z")

</div>

Try expressing your constants as decimal values instead of integers:

```auto
IF p - @start >= 1.00 THEN ... @start := p * 1.00;
ELSEIF @start - p >=1.00 THEN ... @start := p * 1.00;

```

and so on