Ok, I'll bite.:) Why don't you just create the table rfdata the way you want it and change the column types manually. Before you start your Select query delete all rows from the table with "Delete * from rfdata" and then do a "insert into rfdata [(column1, column2 ...)] select ....". If you omit the column names then make sure the column order in the table is the same as the Select query.
Mike
At 10:38 PM 4/13/2004, you wrote:
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]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
