Josh wrote:
> 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.
>
The query as provided doesn't parse--chgID doesn't exist.
Did you try naming the sproc params differently from the corresponding
columns, eg pEMPID, pBDID?
PB
-----
> 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
>
>
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.2.0/275 - Release Date: 3/6/2006
