Peter, nice catch. Changing the parameter names did the trick. Thanks. --- Peter Brawley <[EMAIL PROTECTED]> wrote:
> 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 > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > __________________________________________________ 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]
