> Gregg,
> Monday, May 06, 2002, 6:57:55 PM, you wrote:
>
> GG> Upon reading into the user variables section of the mysql manual, I
decided
> GG> to make my life easier inside several of my queries by specifying some
user
> GG> variables. Note that I'm trying to have everything inside the select
> GG> statement - Primarily because I'm simply passing it through to my data
> GG> mining software that interfaces through myODBC.
>
> GG> I'm trying to accomplish the following query (I'm executing these sql
> GG> queries directly from the command line) --
>
> GG>
============================================================================
> GG> SELECT
> GG> annual_filing.*,
> GG> companies.name AS company_name,
> GG> YEAR(filing_date) AS filing_year,
> GG> (@predictedYear := 1999) AS predictedYear,
> GG> (@earliestYear := 1996) AS earliestYear,
> GG> (@learnYear := 1998) AS learnYear
> GG> FROM
> GG> annual_filing,
> GG> companies
> GG> WHERE
> GG> annual_filing.company_id = companies.ds_id
> GG> AND
> GG> YEAR(filing_date) <= @predictedYear
> GG> AND
> GG> YEAR(filing_date) >= @earliestYear;
> GG>
============================================================================
>
> GG> The above query returned an empty set / no rows.
>
> You should first initialize user variables! By default they contain
> NULL values as SELECT works by first checking WHERE clause and only then
works
> for SELECT.
>
> Check the manual:
> http://www.mysql.com/doc/V/a/Variables.html
According to the manual from the above link, where it states:
==================================================
You can set a variable with the SET syntax:
SET @variable= { integer expression | real expression | string expression }
[,@variable= ...].
You can also assign a value to a variable in statements other than SET.
However, in this case the assignment operator is := rather than =, because =
is reserved for comparisons in non-SET statements:
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?
===
Gregg Graubins <[EMAIL PROTECTED]>
(PGP key available)
---------------------------------------------------------------------
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
