Re: computed columns
On Tue, 27 May 2003 12:18:13 -0400, David Shelley wrote: I'm new to mySQL, but like what I see so far. I'm converting several databases from other products to mySQL 4.0.12 for improved speed and reliability. But I'm having trouble with computed columns. I don't see how to define them. Can't find it in the manual.pdf. You don't see how to define them because I don't think that MySQL (or any SQL engine) has them. Plus, I would bet that using calculated columns violates some type of data normalization, and would also probably slow down MySQL considerably. It sounds like you are trying to convert some FileMaker data tables. Off the top of my head it's the only system that lets (actually forces) you to do calculated values in the data table. (It's my biggest pet complaint about FMP along with repeating values.) I need 2 computed columns, 1st takes the columns fName and lName and appends them together with a space between. 2nd column, numDays, takes sDat (contract start date) and eDat (end date) and calculates eDat-sDat+1. Can someone please help me figure out how to define these columns. You can do what you want on retrieval (or for that matter on INSERTion) with: SELECT CONCAT(fName, ' ', lName) as FullName, (TO_DAYS(eDat) - TO_DAYS(sDat) + 1) as DaysDuration FROM mytable; But, then you probably already knew that. grin --- Listserv only address. Jeff Shapiro -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: computed columns
Actually it's not FileMaker that the old database is in, it's R:Base. I was hoping mySQL supported calculated columns because they're mentioned a couple of times in the manual.pdf for version 4.0.3. Thanks for the suggestion on select concat ... I realize I could do that but I was hoping to make the conversion to mySQL with as few code changes as possible to ensure cross compatability with various databases. I could also solve this with triggers and/or stored procedures if they were supported. Or I could recode the inserts and updates but that would also involve code changes. Dave -Original Message- From: Jeff Shapiro [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 2:53 PM To: [EMAIL PROTECTED] Cc: mySQL Subject: Re: computed columns On Tue, 27 May 2003 12:18:13 -0400, David Shelley wrote: I'm new to mySQL, but like what I see so far. I'm converting several databases from other products to mySQL 4.0.12 for improved speed and reliability. But I'm having trouble with computed columns. I don't see how to define them. Can't find it in the manual.pdf. You don't see how to define them because I don't think that MySQL (or any SQL engine) has them. Plus, I would bet that using calculated columns violates some type of data normalization, and would also probably slow down MySQL considerably. It sounds like you are trying to convert some FileMaker data tables. Off the top of my head it's the only system that lets (actually forces) you to do calculated values in the data table. (It's my biggest pet complaint about FMP along with repeating values.) I need 2 computed columns, 1st takes the columns fName and lName and appends them together with a space between. 2nd column, numDays, takes sDat (contract start date) and eDat (end date) and calculates eDat-sDat+1. Can someone please help me figure out how to define these columns. You can do what you want on retrieval (or for that matter on INSERTion) with: SELECT CONCAT(fName, ' ', lName) as FullName, (TO_DAYS(eDat) - TO_DAYS(sDat) + 1) as DaysDuration FROM mytable; But, then you probably already knew that. grin --- Listserv only address. Jeff Shapiro -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: computed columns
On Tue, 27 May 2003 15:23:28 -0400, David Shelley wrote: Actually it's not FileMaker that the old database is in, it's R:Base. I haven't heard of R:Base in YEARS. I was hoping mySQL supported calculated columns because they're mentioned a couple of times in the manual.pdf for version 4.0.3. What the manual is referring to as calculated columns are the columns in a select statement (like the ones mentioned below). Which isn't what you are wanting. Thanks for the suggestion on select concat ... I realize I could do that but I was hoping to make the conversion to mySQL with as few code changes as possible to ensure cross compatability with various databases. I could also solve this with triggers and/or stored procedures if they were supported. Or I could recode the inserts and updates but that would also involve code changes. Well, off hand. I'd say that you are going to have to do some recoding. Hopefully, someone else will have a better idea. snip snip . You can do what you want on retrieval (or for that matter on INSERTion) with: SELECT CONCAT(fName, ' ', lName) as FullName, (TO_DAYS(eDat) - TO_DAYS(sDat) + 1) as DaysDuration FROM mytable; --- Listserv only address. Jeff Shapiro -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
