[Web-erp-developers] Need some help with MySQL query

[Danie B. <br...@na...>]

I have finalized the second query to this, and it works with some
casting under postgres, however it seems MySQL can not handle such a
query this way. What would be the correct way of doing this in MySQL?

SELECT PurchOrders.IntoStockLocation, PurchOrderDetails.ItemCode,
Sum(PurchOrderDetails.QuantityOrd -
PurchOrderDetails.QuantityRecd) as Order 
FROM PurchOrders, PurchOrderDetails, StockMaster 
WHERE PurchOrders.OrderNo = PurchOrderDetails.OrderNo 
AND PurchOrderDetails.Completed = 0 
AND StockMaster.StockID = PurchOrderDetails.ItemCode 
AND ( PurchOrders.IntoStockLocation, PurchOrderDetails.ItemCode ) IN  (
SELECT LocStock.LocCode, LocStock.StockID 
FROM LocStock, StockMaster 
WHERE LocStock.Quantity <= LocStock.ReorderLevel 
AND LocStock.StockID=StockMaster.StockID )
GROUP BY PurchOrders.IntoStockLocation, PurchOrderDetails.ItemCode
ORDER BY PurchOrders.IntoStockLocation, PurchOrderDetails.ItemCode

Below is the final queries I have decided on, it will limit the amount
of work that needs to be performed by the PHP script

////////////////////////////////////
//
// FINAL QUERIES FOR REORDER REPORT
//
////////////////////////////////////

// To Reorder - produce a list of stock that might to be reorders based
on stock catagory and location
SQL1  = "SELECT LocStock.LocCode, LocStock.StockID, LocStock.Quantity,
LocStock.ReorderLevel ";
SQL1 .= "FROM LocStock, StockMaster ";
SQL1 .= "WHERE LocStock.Quantity <= LocStock.ReorderLevel ";
SQL1 .= "AND
LocStock.StockID::character(20)=StockMaster.StockID::character(20) ";
SQL1 .= "AND StockMaster.CategoryID >= '" . $_POST['FromCriteria'] . "'
";
SQL1 .= "AND StockMaster.CategoryID <= '" . $_POST['ToCriteria'] . "' ";
SQL1 .= "AND LocStock.LocCode = '" . $_POST['Location'] . "' ";
SQL1 .= "ORDER BY LocStock.LocCode, LocStock.StockID ";

// Already Ordered - proude a list of stock that is already ordered and
in the list of possible reorders
SQL2  = "SELECT PurchOrders.IntoStockLocation,
PurchOrderDetails.ItemCode, ";
SQL2 .= "Sum(PurchOrderDetails.QuantityOrd -
PurchOrderDetails.QuantityRecd) as Order ";
SQL2 .= "FROM PurchOrders, PurchOrderDetails, StockMaster ";
SQL2 .= "WHERE PurchOrders.OrderNo = PurchOrderDetails.OrderNo ";
SQL2 .= "AND PurchOrderDetails.Completed = 0 ";
SQL2 .= "AND StockMaster.StockID = PurchOrderDetails.ItemCode ";
SQL2 .= "AND StockMaster.CategoryID >= '" . $_POST['FromCriteria'] . "'
";
SQL2 .= "AND StockMaster.CategoryID <= '" . $_POST['ToCriteria'] . "' ";
SQL2 .= "AND PurchOrders.IntoStockLocation = '" . $_POST['Location'] .
"' ";
SQL2 .= "AND ( PurchOrders.IntoStockLocation, PurchOrderDetails.ItemCode
) IN  (";
SQL2 .= "SELECT LocStock.LocCode, LocStock.StockID ";
SQL2 .= "FROM LocStock, StockMaster ";
SQL2 .= "WHERE LocStock.Quantity <= LocStock.ReorderLevel ";
SQL2 .= "AND LocStock.StockID=StockMaster.StockID ";
SQL2 .= "AND StockMaster.CategoryID >= '" . $_POST['FromCriteria'] . "'
";
SQL2 .= "AND StockMaster.CategoryID <= '" . $_POST['ToCriteria'] . "' ";
SQL2 .= "AND LocStock.LocCode = '" . $_POST['Location'] . "'";
SQL2 .= ") ";
SQL2 .= "GROUP BY PurchOrders.IntoStockLocation,
PurchOrderDetails.ItemCode";
SQL2 .= "ORDER BY PurchOrders.IntoStockLocation,
PurchOrderDetails.ItemCode";

////////////////////////////////////