Gerard Gilliland wrote:
<snip>Michael: Again, Thank you for your excellent suggestions and quick response !! I agree that "Two types of data means you should use two columns." There is no excuse for bad design. (I inherited the database and moved it to MySQL -- I should have converted then.) I will split the Source table into N and Period (probably with better names.) (I am indeed working with a "temp" table. Only for debug purposes and to test before I sent the original problem.)
Concerning:
$qry = "SELECT PlantName, Unit, TagName, CalDate, nPeriod, TRIM(MID(nPeriod,1,INSTR(nPeriod,' ')-1)) AS n, TRIM(MID(nPeriod,INSTR(nPeriod,' ')+1)) AS Period, CASE WHEN Period = 'Month' THEN DATE_ADD(CalDate, INTERVAL n MONTH) WHEN Period = 'Year' THEN DATE_ADD(CalDate, INTERVAL n YEAR) END AS DueDate FROM tblTemp";
Did you try it? (the above) -- Yes It should work. -- I agree. However, It fails. It doesn't work with "Temporary Calculated" columns
<snip>I think it fails for the same reason, I have trouble with using criteria against "DueDate"
Fails (in the WHERE clause): $qry = "SELECT PlantName, Unit, TagName, CalDate, CalPeriod, CalPeriodType, CASE WHEN CalPeriodType = 'Month' THEN DATE_ADD(CalDate, INTERVAL CalPeriod MONTH) WHEN CalPeriodType = 'Year' THEN DATE_ADD(CalDate, INTERVAL CalPeriod YEAR) END AS DueDate FROM tblTemp WHERE DueDate > '2005-01-01'"; (Note: DueDate is a "TemporaryCalculated" field)
Ahh, that's different. You didn't mention trying to use DueDate in the WHERE clause before. You can't do that. The purpose of the WHERE clause is to indicate which rows should be used for your calculations, but you are asking mysql to choose rows based on the result of the calculations. You see the problem? You are asking mysql to pick the rows to operate on based on the result of the operation. In other words, DueDate doesn't exist yet when the WHERE clause is evaluated, so you can't use it there.
Fortunately, there's a solution. The HAVING clause filters rows near the end, after the calculations have been done, so it is the place to use DueDate to limit your results. Thus, this should work:
SELECT PlantName, Unit, TagName, CalDate, CalPeriod, CalPeriodType,
CASE
WHEN CalPeriodType = 'Month'
THEN DATE_ADD(CalDate, INTERVAL CalPeriod MONTH)
WHEN CalPeriodType = 'Year'
THEN DATE_ADD(CalDate, INTERVAL CalPeriod YEAR)
END
AS DueDate
FROM tblTemp
HAVING DueDate > '2005-01-01';Works:
$qry = "SELECT PlantName, Unit, TagName, CalDate, CalPeriod, CalPeriodType,
CASE
WHEN CalPeriodType = 'Month' THEN DATE_ADD(CalDate, INTERVAL
CalPeriod MONTH)
WHEN CalPeriodType = 'Year' THEN DATE_ADD(CalDate, INTERVAL
CalPeriod YEAR)
END
AS DueDate
FROM tblTemp
WHERE
CASE
WHEN CalPeriodType = 'Month' THEN DATE_ADD(CalDate, INTERVAL
CalPeriod MONTH)
WHEN CalPeriodType = 'Year' THEN DATE_ADD(CalDate, INTERVAL
CalPeriod YEAR)
END
> '2005-01-01'";
(Note: Where Criteria does not contain any "TemporaryCalculated" field.)
Well, it does, sort of. Here you force the calculation in the WHERE clause by explicitly doing the calculations. This will work, but it will be less efficient unless the optimizer is smart enough not to do the calculations twice.
A simpler case of the above (but continuing the "TemporaryCalculation" field discussion.)
Fails: $qry = "SELECT PlantName, Unit, TagName, CalDate, CalPeriod, CalPeriodType, (CalPeriod + Unit) AS CalNum FROM tblTemp WHERE CalNum > 7";
Works: $qry = "SELECT PlantName, Unit, TagName, CalDate, CalPeriod, CalPeriodType, (CalPeriod + Unit) AS CalNum FROM tblTemp WHERE (CalPeriod + Unit) > 7";
Right. This is the same as above. The second works by forcing the calculation in the WHERE clause. The first would work if you changed WHERE to HAVING.
I do appreciate the thoroughness of your effort in coversion to Months, However the potential for using Days looms ahead, and the complexity of Days in combinations with Month lengths, and Leap years causes more confusion than the well defined CASE of Day, Month, Year.
Agreed.
I should also point out that because we are selecting rows based on DueDate, and DueDate is a calculated value, there is no index to help us out. In other words, even if there is an index on CalDate, we render it useless (for the purposes of this query) when we feed CalDate into a function to get DueDate. The result is that we are asking for an inefficient full-table scan. Mysql must calculate DueDate for *every* row, then compare the results (in HAVING) to decide which results to show us. Depending on the size of your table and how often this query is run, that may or may not be a problem to you.
It may be advisable to replace the Period and PeriodType columns with a DueDate column. Something like:
ALTER TABLE tblTemp ADD COLUMN DueDate DATE;
UPDATE tblTemp
SET DueDate= CASE
WHEN CalPeriodType = 'Month'
THEN DATE_ADD(CalDate, INTERVAL CalPeriod MONTH)
WHEN CalPeriodType = 'Year'
THEN DATE_ADD(CalDate, INTERVAL CalPeriod YEAR)
END;ALTER TABLE tblTemp DROP COLUMN CalPeriod, DROP COLUMN CalPeriodType;
Then you would do the same calculations when inserting new data. In this case, you can use DueDate in the WHERE clause:
SELECT PlantName, Unit, TagName, CalDate, DueDate FROM tblTemp WHERE DueDate > '2005-01-01';
and an index on DueDate could be used to make finding matching rows very fast.
Again -- Thank you for your excellent support and quick response.
Gerard Gilliland
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
