Difference between revisions of "VbzCart/queries/qryOrderLines notPkgd"
Jump to navigation
Jump to search
(New page: ==Details== * '''Returns''': Active order lines which have yet to be (completely) put into a package * '''Requires''': {{vbzcart/table|ord_lines}}, {{vbzcart/table|ord_pkg_lines}} * '''Use...) |
(this is now obsolete) |
||
| Line 1: | Line 1: | ||
==Details== | ==Details== | ||
| + | * '''Status''': Appears to be OBSOLETE; it's much quicker to generate the list in code (maybe there's a way to optimize the SQL, but code gives you more control over how the summing is done and more options for doing it) | ||
* '''Returns''': Active order lines which have yet to be (completely) put into a package | * '''Returns''': Active order lines which have yet to be (completely) put into a package | ||
| − | * '''Requires''': {{vbzcart | + | * '''Requires''': {{vbzcart|table|ord_lines}}, {{vbzcart|table|ord_pkg_lines}}, {{vbzcart|table|core_orders}} |
| − | * '''Used by''': | + | * '''Used by''': {{vbzcart/query|qryStock_forOpenOrders}} |
| + | * '''History''': | ||
| + | ** '''2009-12-13''' Tried adding JOIN to {{vbzcart/table|core_orders}} to eliminate looking through pulled orders, as a time-optimization, but it didn't seem to make much difference; reverted back to old version. Discovered that doing the summing in code was vastly better, so this query is now OBSOLETE. | ||
==SQL== | ==SQL== | ||
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryOrderLines_notPkgd AS | <section begin=sql /><mysql>CREATE OR REPLACE VIEW qryOrderLines_notPkgd AS | ||
Latest revision as of 12:24, 14 December 2009
Details
- Status: Appears to be OBSOLETE; it's much quicker to generate the list in code (maybe there's a way to optimize the SQL, but code gives you more control over how the summing is done and more options for doing it)
- Returns: Active order lines which have yet to be (completely) put into a package
- Requires: ord_lines, ord_pkg_lines, core_orders
- Used by:
- REDIRECT Template:l/vc/query
- History:
- 2009-12-13 Tried adding JOIN to
- REDIRECT Template:l/vc/table to eliminate looking through pulled orders, as a time-optimization, but it didn't seem to make much difference; reverted back to old version. Discovered that doing the summing in code was vastly better, so this query is now OBSOLETE.
SQL
<mysql>CREATE OR REPLACE VIEW qryOrderLines_notPkgd AS SELECT
oi.ID,
oi.ID_Order,
oi.ID_Item,
oi.CatNum,
oi.Descr,
oi.QtyOrd,
oi.Price,
oi.QtyOrd
-SUM(
IFNULL(pi.QtyShipped,0)
+IFNULL(pi.QtyNotAvail,0)
+IFNULL(pi.QtyKilled,0)
) AS QtyOpen,
SUM(IFNULL(pi.QtyShipped,0)) AS QtyShipped,
SUM(IFNULL(pi.QtyShipped,0)
+IFNULL(pi.QtyNotAvail,0)
+IFNULL(pi.QtyKilled,0)) AS QtyHandled
FROM
ord_lines AS oi LEFT JOIN ord_pkg_lines pi ON pi.ID_OrdLine=oi.ID
GROUP BY
oi.ID, oi.ID_Order, oi.ID_Item, oi.CatNum, oi.Descr, oi.QtyOrd, oi.Price
HAVING oi.QtyOrd-QtyHandled >0;</mysql>