Hi, Gregg Graubins wrote: >>Gregg, >>Monday, May 06, 2002, 6:57:55 PM, you wrote: >> >>GG> Upon reading into the user variables section of the mysql manual, I <cut> <cut> > According to the manual from the above link, where it states: <cut> > mysql> SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3; > +----------------------+------+------+------+ > | @t1:=(@t2:=1)+@t3:=4 | @t1 | @t2 | @t3 | > +----------------------+------+------+------+ > | 5 | 5 | 1 | 4 | > +----------------------+------+------+------+ > ================================================== > > mysql should be SET'ing the user variables in my query above. If mysql is > processing the WHERE first and then the SELECT, shouldn't the user variables > be set anyhow (even if the query returns an empty set)? For example: > > ================================================== > mysql> SELECT > -> annual_filing.*, > -> companies.name AS company_name, > -> YEAR(filing_date) AS filing_year, > -> @predictedYear := 1999 AS predictedYear, > -> @earliestYear := 1996 AS earliestYear, > -> @learnYear := 1998 AS learnYear > -> FROM > -> annual_filing, > -> companies > -> WHERE > -> annual_filing.company_id = companies.ds_id > -> AND > -> YEAR(filing_date) <= @predictedYear > -> AND > -> YEAR(filing_date) >= @earliestYear; > Empty set (25.89 sec) > > mysql> SELECT @predictedYear, @earliestYear, @learnYear; > +----------------+---------------+------------+ > | @predictedYear | @earliestYear | @learnYear | > +----------------+---------------+------------+ > | NULL | NULL | NULL | > +----------------+---------------+------------+ > 1 row in set (0.00 sec) > ================================================== > > If I execute a separate SELECT statement beforehand (Or SET for that matter) > setting the user variables then it works. However, I'm trying to use MyODBC > to connect into the server with my data mining software and I can't perform > multiple queries in this fashion (it expects me to only send one query). > :( Perhaps there's a workaround?
That is correct result. Imagine you have to process this statement manually. Think about order of execution. At 1st step WHERE clause must be processed to find matching rows At 2nd step evrything in SELECT part nust be processed Do you agree with above? If so it must be clear that in you case order in time will be: 1. WHERE ,.. AND YEAR(filing_date) <= @predictedYear 2. SELECT (@predictedYear := 1999) That is why in Where clause your variable is undefined. The same reason prevents you from using grouping functions in WHERE clause like SELECT col ... WHERE col = MAX(col); So as Victoria already suggested you must initialize User variables Before select statement. Or just use common API techniques to generate SQL statement on the fly. I don't see any benefits of using User Variables in your case. Ofcourse you can 'initialize' each variable twice in every statement like: SELECT (@var1 := 1999) as ... WHERE YEAR(filing_date) = (@var1 := 1999) but don't believe it will save you any effort. -- Best regards -- For technical support contracts, visit https://order.mysql.com/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria <___/ www.mysql.com M: +359 88 231668 --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
