Peter, nice catch. Changing the parameter names did
the trick. Thanks.
--- Peter Brawley <peter.brawley@stripped> 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/mysql?unsub=1
>
>
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
