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

[Phil D. <ph...@du...>]

Hi Danie,

This SQL is only part of the story ... what about the quantity on sales orders 
that cannot be delivered because of insufficient stock - we would wish to 
purchase stock to cover these sales too?

I have kept all SQL simple with no subqueries etc to avoid cross SQL server 
issues like this. I would do this in 3 round trips.

One for the demand - which would need to take into account the demand for 
parent assemblies as well.
One for the qty on order and another for the location stock quantites and 
reorder level using PHP to calculate the quantity to buy.

I am confident that there is an answer to how the query could be constructed 
in one bite ...but I'm too simple for that and it is possible that the 
overhead associated with 3 round trips could be less than the LEFT RIGHT 
INSIDE AND OUTSIDE JOINS necessary to acheive this in one SQL script. Also, 
its a bitch to read monster SQL and understand the logic of scripts when the 
SQL is too tough.

Take a look at StockStatus.php for how I did this.


Phil


On Thursday 25 March 2004 21:25, Danie Brink wrote:
> 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";
>
> ////////////////////////////////////
>
>
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by: IBM Linux Tutorials
> Free Linux tutorial presented by Daniel Robbins, President and CEO of
> GenToo technologies. Learn everything from fundamentals to system
> administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
> _______________________________________________
> Web-erp-developers mailing list
> Web...@li...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers