Difference between revisions of "VbzCart/procs/Upd Depts fr Depts Suppliers"

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
(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...)
 
(made it go)
 
(One intermediate revision by the same user not shown)
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)
 +
* '''History''':
 +
** '''2010-11-10''' Fixed syntax so it works; created in database. (None of the procs were actually in the database since it was moved to Rizzo.)
 
==SQL==
 
==SQL==
−
<section begin=sql /><mysql> CREATE PROCEDURE Upd_Depts_fr_Depts_Suppliers()
+
<section begin=sql /><mysql>DELIMITER //
−
    BEGIN
+
DROP PROCEDURE IF EXISTS `Upd_Depts_fr_Depts_Suppliers`//
−
      DELETE FROM _depts;
+
CREATE PROCEDURE Upd_Depts_fr_Depts_Suppliers()
−
      INSERT INTO _depts(`ID`,`ID_Supp`,`CatKey`,`CatNum`,`CatWeb_Dept`,`CatWeb_Title`)
+
BEGIN
−
        SELECT
+
    DELETE FROM _depts;
−
          d.ID,
+
    INSERT INTO _depts(`ID`,`ID_Supp`,`CatKey`,`CatNum`,`CatWeb_Dept`,`CatWeb_Title`)
−
          d.ID_Supplier AS ID_Supp,
+
      SELECT
−
          UPPER(IFNULL(d.PageKey,d.CatKey)) AS CatKey,
+
        d.ID,
−
          UPPER(CONCAT_WS("-",s.CatKey,d.CatKey)) AS CatNum,
+
        d.ID_Supplier AS ID_Supp,
−
          LOWER(CONCAT_WS("/",s.CatKey,IFNULL(d.PageKey,d.CatKey))) AS CatWeb_Dept,
+
        UPPER(IFNULL(d.PageKey,d.CatKey)) AS CatKey,
−
          LOWER(CONCAT_WS("/",s.CatKey,d.CatKey)) AS CatWeb_Title
+
        UPPER(CONCAT_WS("-",s.CatKey,d.CatKey)) AS CatNum,
−
        FROM depts AS d LEFT JOIN suppliers AS s ON d.ID_Supplier=s.ID;
+
        LOWER(CONCAT_WS("/",s.CatKey,IFNULL(d.PageKey,d.CatKey))) AS CatWeb_Dept,
−
    END</mysql>
+
        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//
 +
DELIMITER ;</mysql>
 
<section end=sql />
 
<section end=sql />

Latest revision as of 12:18, 10 November 2010

About

  • Purpose: fills _depts (does not set inPrint/inStock fields)
  • Input: cat_depts, cat_supp
  • Output: _depts (delete/insert)
  • History:
    • 2010-11-10 Fixed syntax so it works; created in database. (None of the procs were actually in the database since it was moved to Rizzo.)

SQL

<mysql>DELIMITER // DROP PROCEDURE IF EXISTS `Upd_Depts_fr_Depts_Suppliers`// 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// DELIMITER ;</mysql>

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