RE: Changing types on the fly in select queries?
> You might be able to use the CAST() function. > > http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html Unfortunately our producation machine is using MySQL 3.23.x and from what I've found the cast() function isn't available. :( "CAST() and CONVERT() are available as of MySQL 4.0.2."
RE: Changing types on the fly in select queries?
> You can cast data tyes explicitly: > http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html Now that is cool. Too bad we are using MySQL 3.23.x on our production box. I can't use it. :( > If this table is going to get large then you might find it too slow to use > that method. Your query is > logically the same as this (assuming integer columns): > > WHERE > (year > 2007) OR > (year = 2007 AND period > 3) OR > (year = 2007 AND period = 3 AND week > 1) > ; I'm not quite sure if this will work. For the following reasons. - period is of type char(2) so I can have the leading zero for the concat(). Is it possible to compare a char() and an int() value? example '4' > 2? - The values in the database for period are 01 through 13. The values of week are 1 through 4. The values of year is obvious. With the way the values are set I'm not sure if this WHERE you have will work on not. Guess I'll have to do some testing. > Can't remember off the top of my head if that would be able to use an index > on (year,period,week) > though. Anyone? That would be intersting to see if that did work. Thanks for the help!
RE: Changing types on the fly in select queries?
>WHERE where concat(year,period,week) as type int < 2007031 > >Note that I'm trying to change the type of what the concat() is doing. >Is this even possible? If so is it possible to do it in the WHERE? You can cast data tyes explicitly: http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html >The reason why I think I need to do this is that 'period' is a char(2). >I have to have the leading zero for every entry into the database so I >can run my less than compare to it. Is there a better way of doing this >than having the 'period' a char(2) type and trying to make whole >concat() a type of int() on the fly? If this table is going to get large then you might find it too slow to use that method. Your query is logically the same as this (assuming integer columns): WHERE (year > 2007) OR (year = 2007 AND period > 3) OR (year = 2007 AND period = 3 AND week > 1) ; Can't remember off the top of my head if that would be able to use an index on (year,period,week) though. Anyone? Good luck, James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Changing types on the fly in select queries?
At 12:48 -0600 12/27/05, Jay Paulson \(CE CEN\) wrote: I have a strange question for you all. I've inherated some code and the way the code works is that I can only mess with the WHERE part of a query. Therefore, I was wondering if something like this would be possible. WHERE where concat(year,period,week) as type int < 2007031 Note that I'm trying to change the type of what the concat() is doing. Is this even possible? If so is it possible to do it in the WHERE? The reason why I think I need to do this is that 'period' is a char(2). I have to have the leading zero for every entry into the database so I can run my less than compare to it. Is there a better way of doing this than having the 'period' a char(2) type and trying to make whole concat() a type of int() on the fly? Thanks! You might be able to use the CAST() function. http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Changing types on the fly in select queries?
Generally MySQL does format conversion for you to match data types. You can also force the CONCAT result to be integer by the following where concat(year,period,week) + 0 < 2007031 ^^^ -Original Message- From: Jay Paulson (CE CEN) [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 27, 2005 12:49 PM To: mysql@lists.mysql.com Subject: Changing types on the fly in select queries? I have a strange question for you all. I've inherated some code and the way the code works is that I can only mess with the WHERE part of a query. Therefore, I was wondering if something like this would be possible. WHERE where concat(year,period,week) as type int < 2007031 Note that I'm trying to change the type of what the concat() is doing. Is this even possible? If so is it possible to do it in the WHERE? The reason why I think I need to do this is that 'period' is a char(2). I have to have the leading zero for every entry into the database so I can run my less than compare to it. Is there a better way of doing this than having the 'period' a char(2) type and trying to make whole concat() a type of int() on the fly? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
