VbzCart/docs/procs/Upd Depts fr Depts Suppliers: Difference between revisions

From Woozle Writes Code
< VbzCart‎ | docs‎ | procs
Jump to navigation Jump to search
imported>Woozle
(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...)
 
imported>Woozle
(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> CREATE PROCEDURE Upd_Depts_fr_Depts_Suppliers()
<section begin=sql /><mysql>CREATE PROCEDURE Upd_Depts_fr_Depts_Suppliers()
    BEGIN
  BEGIN
      DELETE FROM _depts;
    DELETE FROM _depts;
      INSERT INTO _depts(`ID`,`ID_Supp`,`CatKey`,`CatNum`,`CatWeb_Dept`,`CatWeb_Title`)
    INSERT INTO _depts(`ID`,`ID_Supp`,`CatKey`,`CatNum`,`CatWeb_Dept`,`CatWeb_Title`)
        SELECT
      SELECT
          d.ID,
        d.ID,
          d.ID_Supplier AS ID_Supp,
        d.ID_Supplier AS ID_Supp,
          UPPER(IFNULL(d.PageKey,d.CatKey)) AS CatKey,
        UPPER(IFNULL(d.PageKey,d.CatKey)) AS CatKey,
          UPPER(CONCAT_WS("-",s.CatKey,d.CatKey)) AS CatNum,
        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,IFNULL(d.PageKey,d.CatKey))) AS CatWeb_Dept,
          LOWER(CONCAT_WS("/",s.CatKey,d.CatKey)) AS CatWeb_Title
        LOWER(CONCAT_WS("/",s.CatKey,d.CatKey)) AS CatWeb_Title
        FROM depts AS d LEFT JOIN suppliers AS s ON d.ID_Supplier=s.ID;
      FROM cat_depts AS d LEFT JOIN cat_supp AS s ON d.ID_Supplier=s.ID;
    END</mysql>
  END</mysql>
<section end=sql />
<section end=sql />

Revision as of 21:20, 8 March 2009

About

SQL

<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 />

Retrieved from "https://wooz.dev/mw/index.php?title=VbzCart/docs/procs/Upd_Depts_fr_Depts_Suppliers&oldid=4853"