From: Peter Brawley
Date: March 10 2006 2:05pm
Subject: Re: Stored Procedure Problem
List-Archive: http://lists.mysql.com/mysql/195621
Message-Id: <44118793.1030601@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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