VbzCart/docs/procs/Upd Depts fr DeptIttyps: Difference between revisions
Jump to navigation
Jump to search
imported>Woozle (created in db) |
m (Woozle moved page VbzCart/VbzCart/procs/Upd Depts fr DeptIttyps to VbzCart/docs/procs/Upd Depts fr DeptIttyps without leaving a redirect: part 2) |
||
| (2 intermediate revisions by one other user not shown) | |||
| Line 1: | Line 1: | ||
==About== | ==About== | ||
* '''Purpose''': Updates some fields in {{vbzcart|table|_depts}} after that has been filled in by {{vbzcart|proc|Upd_Depts_fr_Depts_Suppliers}} | * '''Purpose''': Updates some fields in {{vbzcart|table|_depts}} after that has been filled in by {{vbzcart|proc|Upd_Depts_fr_Depts_Suppliers}} | ||
* '''Input''': {{vbzcart| | * '''Input''': {{vbzcart/query|qryCat_Titles_Item_stats}} (group by ID_Dept) | ||
* '''Output''': {{vbzcart|table|_depts}} (update) | * '''Output''': {{vbzcart|table|_depts}} (update) | ||
* '''History''': | * '''History''': | ||
| Line 7: | Line 7: | ||
*** Added DROP PROCEDURE for easier maintenance. | *** Added DROP PROCEDURE for easier maintenance. | ||
*** Created in database (procs were not ported from L48 to Rizzo). | *** Created in database (procs were not ported from L48 to Rizzo). | ||
** '''2011-12-24''' this was in a long recursive loop: '''this''' → {{vbzcart|table|_depts}} → {{vbzcart|proc|Upd_Titles_fr_Depts}} → {{vbzcart|table|_titles}} → {{vbzcart|proc|Upd_TitleIttyps_fr_Titles}} → {{vbzcart|table|_title_ittyps}} → {{vbzcart|proc|Upd_DeptIttyps_fr_TitleIttyps}} → {{vbzcart|table|_dept_ittyps}} → '''this''', so replacing source {{vbzcart|table|_dept_ittyps}} with {{vbzcart/query|qryCat_Titles_Item_stats}} | |||
==SQL== | ==SQL== | ||
<section begin=sql /><mysql>DROP PROCEDURE IF EXISTS Upd_Depts_fr_DeptIttyps; | <section begin=sql /><mysql>DROP PROCEDURE IF EXISTS Upd_Depts_fr_DeptIttyps; | ||
| Line 15: | Line 16: | ||
SUM(di.cntForSale) AS cntForSale, | SUM(di.cntForSale) AS cntForSale, | ||
SUM(di.cntInPrint) AS cntInPrint, | SUM(di.cntInPrint) AS cntInPrint, | ||
SUM(di. | SUM(di.qtyForSale) AS qtyInStock | ||
FROM | FROM qryCat_Titles_Item_stats AS di GROUP BY ID_Dept | ||
) AS di ON di.ID_Dept=d.ID | ) AS di ON di.ID_Dept=d.ID | ||
SET | SET | ||
Latest revision as of 01:55, 25 February 2024
About
- Purpose: Updates some fields in Template:Vbzcart after that has been filled in by Template:Vbzcart
- Input: Template:Vbzcart/query (group by ID_Dept)
- Output: Template:Vbzcart (update)
- History:
- 2010-11-10
- Added DROP PROCEDURE for easier maintenance.
- Created in database (procs were not ported from L48 to Rizzo).
- 2011-12-24 this was in a long recursive loop: this → Template:Vbzcart → Template:Vbzcart → Template:Vbzcart → Template:Vbzcart → Template:Vbzcart → Template:Vbzcart → Template:Vbzcart → this, so replacing source Template:Vbzcart with Template:Vbzcart/query
- 2010-11-10
SQL
<section begin=sql /><mysql>DROP PROCEDURE IF EXISTS Upd_Depts_fr_DeptIttyps; CREATE PROCEDURE Upd_Depts_fr_DeptIttyps()
UPDATE _depts AS d LEFT JOIN (
SELECT
ID_Dept,
SUM(di.cntForSale) AS cntForSale,
SUM(di.cntInPrint) AS cntInPrint,
SUM(di.qtyForSale) AS qtyInStock
FROM qryCat_Titles_Item_stats AS di GROUP BY ID_Dept
) AS di ON di.ID_Dept=d.ID
SET
d.cntForSale = di.cntForSale,
d.cntInPrint = di.cntInPrint,
d.qtyInStock = di.qtyInStock;</mysql>
<section end=sql />