Greetings,
Upon reading into the user variables section of the mysql manual, I decided
to make my life easier inside several of my queries by specifying some user
variables. Note that I'm trying to have everything inside the select
statement - Primarily because I'm simply passing it through to my data
mining software that interfaces through myODBC.
I'm trying to accomplish the following query (I'm executing these sql
queries directly from the command line) --
============================================================================
==
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;
============================================================================
==
The above query returned an empty set / no rows.
Replacing the user variables with actual values, as shown below, returns the
appropriate rows (5537 rows) (for obvious reasons because I'm not using any
user variables in the WHERE clause):
============================================================================
==
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) <= 1999
AND
YEAR(filing_date) >= 1996;
============================================================================
==
I also tried the below ...
============================================================================
==
SET @predictedYear = 1999;
SET @earliestYear = 1996;
SET @learnYear = 1998;
SELECT
company_id,
@findEarliestYear AS findEarliestYear,
@findLearnYear AS findLearnYear,
@findPredictedYear AS findPredictedYear
FROM
annual_filing
WHERE
YEAR(annual_filing.filing_date) > @findPredictedYear;
============================================================================
==
And mysql returned no results / an empty set.
Trying to debug the problem further, I tried ...
============================================================================
==
SET @predictedYear = 1999;
SET @earliestYear = 1996;
SET @learnYear = 1998;
SELECT
company_id,
@findEarliestYear,
@findLearnYear,
@findPredictedYear
FROM
annual_filing
WHERE
YEAR(annual_filing.filing_date) > @findPredictedYear;
============================================================================
==
Still an empty set...
And also verified the SET is actually doing its job (which it is) --
============================================================================
==
mysql> SET @predictedYear = 1999;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @earliestYear = 1996;
Query OK, 0 rows affected (0.01 sec)
mysql> SET @learnYear = 1998;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @predictedYear, @earliestYear, @learnYear;
+----------------+---------------+------------+
| @predictedYear | @earliestYear | @learnYear |
+----------------+---------------+------------+
| 1999 | 1996 | 1998 |
+----------------+---------------+------------+
1 row in set (0.00 sec)
============================================================================
==
Does anyone have any insights or clues as to what the problem could be?
Thanks in advance!
===
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
