Difference between revisions of "VbzCart/procs/Upd DeptIttyps fr TitleIttyps"

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
< VbzCart‎ | procs
Jump to navigation Jump to search
(+ID_Supp)
m (GROUP clause fix)
 
Line 19: Line 19:
 
     SUM(ti.qtyInStock) AS qtyInStock
 
     SUM(ti.qtyInStock) AS qtyInStock
 
   FROM _title_ittyps AS ti LEFT JOIN cat_depts AS d ON ti.ID_Dept=d.ID
 
   FROM _title_ittyps AS ti LEFT JOIN cat_depts AS d ON ti.ID_Dept=d.ID
−
   GROUP BY ID_ItTyp, ID_Dept, ID_Supp;</mysql>
+
   GROUP BY ID_ItTyp, ID_Dept, ID_Supplier;</mysql>
 
<section end=sql />
 
<section end=sql />

Latest revision as of 19:51, 2 February 2011

About

  • Input: _title_ittyps, cat_depts (grouped by ID_ItTyp, ID_Dept, (ID_Supp redundant))
  • Output: _dept_ittyps (replace)
  • History:
    • 2010-11-10
      • Added DROP PROCEDURE for easier maintenance.
      • Created in database (procs were not ported from L48 to Rizzo).
    • 2011-02-02 added join with cat_depts so we can update new field ID_Supp

SQL

<mysql>DROP PROCEDURE IF EXISTS Upd_DeptIttyps_fr_TitleIttyps; CREATE PROCEDURE Upd_DeptIttyps_fr_TitleIttyps() 

 REPLACE INTO _dept_ittyps(ID_ItTyp,ID_Dept,ID_Supp,cntForSale,cntInPrint,qtyInStock)
 SELECT
   ti.ID_ItTyp,
   ti.ID_Dept,
   d.ID_Supplier,
   SUM(ti.cntForSale) AS cntForSale,
   SUM(ti.cntInPrint) AS cntInPrint,
   SUM(ti.qtyInStock) AS qtyInStock
 FROM _title_ittyps AS ti LEFT JOIN cat_depts AS d ON ti.ID_Dept=d.ID
 GROUP BY ID_ItTyp, ID_Dept, ID_Supplier;</mysql>

Retrieved from "https://htyp.org/mw/index.php?title=VbzCart/procs/Upd_DeptIttyps_fr_TitleIttyps&oldid=14928"