From: Peter Brawley
Date: April 22 2009 4:20pm
Subject: Re: 1064 errors
List-Archive: http://lists.mysql.com/mysql/217233
Message-Id: <49EF43CB.50101@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------070905080505050700080001"

--------------070905080505050700080001
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Matthew,

 >CREATE PROCEDURE 'xxxxx'.'CrossSelling' ()
 >BEGIN
 >SELECT TOP 5 OrderDetails.ProductID, OrderDetails.ProductName, 
Count(OrderDetails.ProductID) AS CountOfProductID
 >FROM OrderDetails
 >WHERE (((OrderDetails.OrderID) In (select OrderID from OrderDetails 
where ProductID=[pid])))
 >GROUP BY OrderDetails.ProductID, OrderDetails.ProductName
 >HAVING (((OrderDetails.ProductID)<>[pid]))
 >ORDER BY Count(OrderDetails.ProductID) DESC;
 >END

MySQL syntax != MSSQL syntax. No TOP in MySQL---use LIMIT (and it's 
slower). Also IN(SELECT...) is abysmally slow. For alternatives see "The 
unbearable slowness of IN()" at 
http://www.artfulsoftware.com/infotree/queries.php.

PB

-----

Matthew Stuart wrote:
> Hi, I have several procedures that I have taken from an old Microsoft 
> database, and I have tired to use them in a MySQL 5.1.32 database, but 
> I am getting errors when trying to input them. There are three in 
> total that I am struggling with and would appreciate some guidance...
>
> This is a cross selling query:
>
> CREATE PROCEDURE 'xxxxx'.'CrossSelling' ()
> BEGIN
> SELECT TOP 5 OrderDetails.ProductID, OrderDetails.ProductName, 
> Count(OrderDetails.ProductID) AS CountOfProductID
> FROM OrderDetails
> WHERE (((OrderDetails.OrderID) In (select OrderID from OrderDetails 
> where ProductID=[pid])))
> GROUP BY OrderDetails.ProductID, OrderDetails.ProductName
> HAVING (((OrderDetails.ProductID)<>[pid]))
> ORDER BY Count(OrderDetails.ProductID) DESC;
> END
>
> Error is: 1064
>
> '5 OrderDetails.ProductID, OrderDetails.ProductName, 
> Count(OrderDetails.ProductID' at line 3
>
>
>
> If somebody could give me an idea of what is wrong here with regards 
> to it working with MySQL, I might be able to make the other two 
> problem functions work with out too many tears.
>
> Thanks.
>
> Mat
> ------------------------------------------------------------------------
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com 
> Version: 8.0.238 / Virus Database: 270.12.2/2074 - Release Date: 04/22/09 08:49:00
>
>   

--------------070905080505050700080001--