I posted this same item on the mysql forum but the
only place that looked remotely appropriate was under
the Newbie section... I'm not sure if it will be
answer there so I thought I might toss it out here to
see if there were any takers.
I'm baffled as to why this stored procedure is acting
this way. See the below sample table and examples.
mysql> select * from Rates;
+------+------+-------+-------------+---------------+
| rtID | bdID | empID | rtStartDate | rtBillingRate |
+------+------+-------+-------------+---------------+
| 1 | NULL | NULL | 0000-00-00 | 0.00 |
| 2 | NULL | 1 | 2004-01-01 | 2.00 |
| 3 | NULL | 1 | 2004-05-10 | 4.00 |
| 4 | NULL | 1 | 2005-01-10 | 6.00 |
| 5 | NULL | 1 | 2005-04-12 | 8.00 |
| 6 | NULL | 1 | 2006-01-02 | 10.00 |
| 8 | 37 | 1 | 2005-10-01 | 25.00 |
+------+------+-------+-------------+---------------+
DELIMITER $
CREATE PROCEDURE test_rate (EMPID int, BDID int,
CURRENTDATE date)
BEGIN
SELECT
COALESCE(rt3.rtBillingRate,rt2.rtBillingRate,rt1.rtBillingRate)
AS rtBillingRate
FROM Rates rt1
LEFT JOIN Rates rt2 ON (rt2.empID=EMPID and
rt2.chgID IS NULL and rt2.bdID IS NULL and
rt2.rtStartDate=(SELECT MAX(rtStartDate) FROM Rates
WHERE empID=EMPID and chgID IS NULL and bdID IS NULL
and rtStartDate <= CURRENTDATE))
LEFT JOIN Rates rt3 ON (rt3.empID=EMPID and
rt3.chgID IS NULL and rt3.bdID=BDID and
rt3.rtStartDate=(SELECT MAX(rtStartDate) FROM Rates
WHERE empID=EMPID and chgID IS NULL and bdID=BDID and
rtStartDate <= CURRENTDATE))
WHERE rt1.empID IS NULL
and rt1.chgID IS NULL;
END$
DELIMITER ;
mysql> call test_rate (1,NULL,'2005-09-01');
+---------------+
| rtBillingRate |
+---------------+
| 8.00 |
+---------------+
CORRECT!
mysql> call test_rate (1,37,'2005-10-10');
+---------------+
| rtBillingRate |
+---------------+
| 25.00 |
+---------------+
CORRECT!
mysql> call test_rate (1,NULL,'2005-10-10');
+---------------+
| rtBillingRate |
+---------------+
| 0.00 |
+---------------+
1 row in set (0.01 sec)
WRONG! This should have returned 8.00.
When I run this query by itself (outside the
procedure) I get the correct result: (notice I'm
plugging in EMPID, BDID, and CURRENTDATE parameters)
SELECT
COALESCE(rt3.rtBillingRate,rt2.rtBillingRate,rt1.rtBillingRate)
AS rtBillingRate
FROM Rates rt1
LEFT JOIN Rates rt2 ON (rt2.empID=1 and
rt2.chgID IS NULL and rt2.bdID IS NULL and
rt2.rtStartDate=(SELECT MAX(rtStartDate) FROM Rates
WHERE empID=1 and chgID IS NULL and bdID IS NULL and
rtStartDate <= '2005-10-10'))
LEFT JOIN Rates rt3 ON (rt3.empID=1 and
rt3.chgID IS NULL and rt3.bdID=NULL and
rt3.rtStartDate=(SELECT MAX(rtStartDate) FROM Rates
WHERE empID=1 and chgID IS NULL and bdID=NULL and
rtStartDate <= '2005-10-10'))
WHERE rt1.empID IS NULL
and rt1.chgID IS NULL;
+---------------+
| rtBillingRate |
+---------------+
| 8.00 |
+---------------+
1 row in set (0.00 sec)
CORRECT!
What's going wrong in the stored procedure?
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
