I'm doing a select where I lag across records, and would like to compute some differences. It seems to do the calcs right if I evaluate the result in an IF statement, but if I just want to get the calculation result stored, it seems to default to a data type that only stores on digit. Here's the offending code:
create table rfdata SELECT t1.*, dayname( t1.tradedate ) AS tradedayofweek, dayofmonth( t1.tradedate ) AS trademonthday, dayofyear( t1.tradedate ) AS tradedoy, monthname( t1.tradedate ) AS trademonth, ((t2.currentPrice - t1.currentPrice) / t1.currentPrice) AS d1closechange, ((t2.dayMinPrice - t1.currentPrice) / t1.currentPrice) AS d1lowchange, ((t2.dayMaxPrice - t1.currentPrice) / t1.currentPrice) AS d1highchange, if(((t2.dayMaxPrice-t1.currentPrice)/t1.currentPrice)>.03,1,0) AS d1threepcthit, ((t3.open - t1.currentPrice) / t1.currentPrice) AS d2openchange, if(((t3.open-t1.currentPrice)/t1.currentPrice)>.02,1,0) AS d2twopct FROM sorted_data AS t1 LEFT JOIN sorted_data AS t2 ON t2.newid = t1.newid +1 AND t2.symbol = t1.symbol LEFT JOIN sorted_data AS t3 ON t3.newid = t1.newid +2 AND t3.symbol = t1.symbol; This give me a able with the following offending results: +--------------+---------------+ | d1highchange | d1threepcthit | +--------------+---------------+ | 0.0 | 0 | | 0.0 | 1 | | 0.0 | 0 | Where d1highchange should have been a decimal that was over .03, given the "1" in the second column. If I look at the 1st column in phpMyAdmin, it appears to be type "Double", with "25,1" in the defaults/format display. Any way I can rewrite my table creation code to be sure that value gets stored out several decimal places? Thanks, I looked in the manual but didn't see anything about column typing in a CREATE ... SELECT query. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
