Difference between revisions of "VbzCart/procs/Upd Depts fr Depts Suppliers"
Jump to navigation
Jump to search
(New page: ==About== * '''Purpose''': fills {{vbzcart|table|_depts}} (does not set inPrint/inStock fields) ==SQL== <section begin=sql /><mysql> CREATE PROCEDURE Upd_Depts_fr_Depts_Suppliers() BE...) |
(updated source table names; more doc) |
||
Line 1: | Line 1: | ||
==About== | ==About== | ||
* '''Purpose''': fills {{vbzcart|table|_depts}} (does not set inPrint/inStock fields) | * '''Purpose''': fills {{vbzcart|table|_depts}} (does not set inPrint/inStock fields) | ||
+ | * '''Input''': {{vbzcart|table|cat_depts}}, {{vbzcart|table|cat_supp}} | ||
+ | * '''Output''': {{vbzcart|table|_depts}} (delete/insert) | ||
==SQL== | ==SQL== | ||
− | <section begin=sql /><mysql> | + | <section begin=sql /><mysql>CREATE PROCEDURE Upd_Depts_fr_Depts_Suppliers() |
− | + | BEGIN | |
− | + | DELETE FROM _depts; | |
− | + | INSERT INTO _depts(`ID`,`ID_Supp`,`CatKey`,`CatNum`,`CatWeb_Dept`,`CatWeb_Title`) | |
− | + | SELECT | |
− | + | d.ID, | |
− | + | d.ID_Supplier AS ID_Supp, | |
− | + | UPPER(IFNULL(d.PageKey,d.CatKey)) AS CatKey, | |
− | + | UPPER(CONCAT_WS("-",s.CatKey,d.CatKey)) AS CatNum, | |
− | + | LOWER(CONCAT_WS("/",s.CatKey,IFNULL(d.PageKey,d.CatKey))) AS CatWeb_Dept, | |
− | + | LOWER(CONCAT_WS("/",s.CatKey,d.CatKey)) AS CatWeb_Title | |
− | + | FROM cat_depts AS d LEFT JOIN cat_supp AS s ON d.ID_Supplier=s.ID; | |
− | + | END</mysql> | |
<section end=sql /> | <section end=sql /> |
Revision as of 21:20, 8 March 2009
About
- Purpose: fills _depts (does not set inPrint/inStock fields)
- Input: cat_depts, cat_supp
- Output: _depts (delete/insert)
SQL
<mysql>CREATE PROCEDURE Upd_Depts_fr_Depts_Suppliers()
BEGIN DELETE FROM _depts; INSERT INTO _depts(`ID`,`ID_Supp`,`CatKey`,`CatNum`,`CatWeb_Dept`,`CatWeb_Title`) SELECT d.ID, d.ID_Supplier AS ID_Supp, UPPER(IFNULL(d.PageKey,d.CatKey)) AS CatKey, UPPER(CONCAT_WS("-",s.CatKey,d.CatKey)) AS CatNum, LOWER(CONCAT_WS("/",s.CatKey,IFNULL(d.PageKey,d.CatKey))) AS CatWeb_Dept, LOWER(CONCAT_WS("/",s.CatKey,d.CatKey)) AS CatWeb_Title FROM cat_depts AS d LEFT JOIN cat_supp AS s ON d.ID_Supplier=s.ID; END</mysql>