VbzCart/queries/qryOrderLines notPkgd

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
< VbzCart‎ | queries
Revision as of 21:07, 3 December 2008 by Woozle (talk | contribs) (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...)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

Details

  • Returns: Active order lines which have yet to be (completely) put into a package
  • Requires:
  1. REDIRECT Template:l/vc/table, ord_pkg_lines
  • Used by:

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>

Retrieved from "https://htyp.org/mw/index.php?title=VbzCart/queries/qryOrderLines_notPkgd&oldid=10909"