web-erp-developers

[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";

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

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

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

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

On Fri, 2004-03-26 at 01:24, Phil Daintree wrote:
> 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.
Thanks, you have just highlighted that I have not as yet considered every thing,
the one thing that covers the report for now, is that it is just a
suggestion, however once again it should be reported that these late
delivery problems should also be highlighted.

I need to rethink this, with everything involved, including assemblies
and whether the products are manufactured etc. So I will come back with
something better.

Thanks


> 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.
I will, thanks. I was using stockValuation as a template.

Kind Regards
Danie Brink br...@na...


> 
> 
> 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
> 
> 
> 
> -------------------------------------------------------
> 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
>