Re: Conditional in update
2014/02/11 18:14 -0500, Larry Martell set LIMIT = sign(LIMIT) * 100 * floor(0.01 + (sign(LIMIT) * LIMIT * ratio/100) The function TRUNCATE can be useful here: set LIMIT = TRUNCATE(LIMIT * ratio + 0.01 * sign(LIMIT), -2) , if it works as advertized. In any case, ABS(LIMIT) = sign(LIMIT) * LIMIT . As for limiting the value, see this (clipping can be useful to you): 11.2.6. Out-of-Range and Overflow Handling When MySQL stores a value in a numeric column that is outside the permissible range of the column data type, the result depends on the SQL mode in effect at the time: * If strict SQL mode is enabled, MySQL rejects the out-of-range value with an error, and the insert fails, in accordance with the SQL standard. * If no restrictive modes are enabled, MySQL clips the value to the appropriate endpoint of the range and stores the resulting value instead. When an out-of-range value is assigned to an integer column, MySQL stores the value representing the corresponding endpoint of the column data type range. If you store 256 into a file:///C:/PROGRA%7E1/MySQL/MYSQLD%7E1.14/HTML/data-types.html#integer-typesTINYINT or TINYINT UNSIGNED column, MySQL stores 127 or 255, respectively. When a floating-point or fixed-point column is assigned a value that exceeds the range implied by the specified (or default) precision and scale, MySQL stores the value representing the corresponding endpoint of that range. Column-assignment conversions that occur due to clipping when MySQL is not operating in strict mode are reported as warnings for file:///C:/PROGRA%7E1/MySQL/MYSQLD%7E1.14/HTML/sql-syntax.html#alter-tableALTER TABLE, file:///C:/PROGRA%7E1/MySQL/MYSQLD%7E1.14/HTML/sql-syntax.html#load-dataLOAD DATA INFILE, file:///C:/PROGRA%7E1/MySQL/MYSQLD%7E1.14/HTML/sql-syntax.html#updateUPDATE, and multiple-row file:///C:/PROGRA%7E1/MySQL/MYSQLD%7E1.14/HTML/sql-syntax.html#insertINSERT statements. In strict mode, these statements fail, and some or all the values will not be inserted or changed, depending on whether the table is a transactional table and other factors. For details, see file:///C:/PROGRA%7E1/MySQL/MYSQLD%7E1.14/HTML/server-administration.html#server-sql-modeSection 5.1.7, Server SQL Modes. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Conditional in update
On Wed, Feb 12, 2014 at 5:35 PM, h...@tbbs.net wrote: 2014/02/11 18:14 -0500, Larry Martell set LIMIT = sign(LIMIT) * 100 * floor(0.01 + (sign(LIMIT) * LIMIT * ratio/100) The function TRUNCATE can be useful here: set LIMIT = TRUNCATE(LIMIT * ratio + 0.01 * sign(LIMIT), -2) , if it works as advertized. In any case, ABS(LIMIT) = sign(LIMIT) * LIMIT . As for limiting the value, see this (clipping can be useful to you): 11.2.6. Out-of-Range and Overflow Handling When MySQL stores a value in a numeric column that is outside the permissible range of the column data type, the result depends on the SQL mode in effect at the time: * If strict SQL mode is enabled, MySQL rejects the out-of-range value with an error, and the insert fails, in accordance with the SQL standard. * If no restrictive modes are enabled, MySQL clips the value to the appropriate endpoint of the range and stores the resulting value instead. When an out-of-range value is assigned to an integer column, MySQL stores the value representing the corresponding endpoint of the column data type range. If you store 256 into a file:///C:/PROGRA%7E1/MySQL/MYSQLD%7E1.14/HTML/data-types.html#integer-typesTINYINT or TINYINT UNSIGNED column, MySQL stores 127 or 255, respectively. When a floating-point or fixed-point column is assigned a value that exceeds the range implied by the specified (or default) precision and scale, MySQL stores the value representing the corresponding endpoint of that range. Column-assignment conversions that occur due to clipping when MySQL is not operating in strict mode are reported as warnings for file:///C:/PROGRA%7E1/MySQL/MYSQLD%7E1.14/HTML/sql-syntax.html#alter-tableALTER TABLE, file:///C:/PROGRA%7E1/MySQL/MYSQLD%7E1.14/HTML/sql-syntax.html#load-dataLOAD DATA INFILE, file:///C:/PROGRA%7E1/MySQL/MYSQLD%7E1.14/HTML/sql-syntax.html#updateUPDATE, and multiple-row file:///C:/PROGRA%7E1/MySQL/MYSQLD%7E1.14/HTML/sql-syntax.html#insertINSERT statements. In strict mode, these statements fail, and some or all the values will not be inserted or changed, depending on whether the table is a transactional table and other factors. For details, see file:///C:/PROGRA%7E1/MySQL/MYSQLD%7E1.14/HTML/server-administration.html#server-sql-modeSection 5.1.7, “Server SQL Modes”. Thanks for the reply. I was able to do this with a case statement, but then the requirements were changed and I had to know when I constrained the limit so I could log it to a file. So I ended up just doing the update as it was originally, then adding a select after to find any rows that exceeded the limit, and then updating those to the max or min, and then I could log them to a file. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Conditional in update
Is there some way I can have a conditional in an update?
I have this update sql (paraphrased):
update LMPROC_LIMITS
set LIMIT = sign(LIMIT) * 100 * floor(0.01 + (sign(LIMIT) * LIMIT
* ratio/100)
where SYMBOL_ID = symbolId
and CLASSTYPE = LimitType
and TYPE_ in ('minClusterPosition', 'maxClusterPosition',
'minProductPosition', 'maxProductPosition', 'minBookPosition',
'maxBookPosition', 'maxShortShares')
(ratio, symbolId, and LimitType all are filled in at run time)
What i want to do is limit (no pun intended) the value LIMIT is set to
to +/-2,147,483,647 - i.e. if the result of (sign(LIMIT) * 100 *
floor(0.01 + (sign(LIMIT) * LIMIT * ratio/100)) is more or less
then that, I want to set it to +/-2,147,483,647 Can I do this in SQL?
Thanks!
-larry
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
