VbzCart/docs/procs/Upd Titles fr Depts: 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|_titles}} ==SQL== <section begin=sql /><mysql> CREATE PROCEDURE Upd_Titles_fr_Depts() REPLACE INTO _titles(ID,ID_Supp,CatNum,CatWeb) ...)
 
imported>Woozle
(fixed obsoleteness)
Line 1: Line 1:
==About==
==About==
* '''Purpose''': Fills {{vbzcart|table|_titles}}
* '''Input''': {{vbzcart|table|cat_titles}}, {{vbzcart|table|_depts}}
* '''Output''': {{vbzcart|table|_titles}}
* '''History''':
** '''2010-11-10'''
*** Revised SQL to use "cat_titles" instead of "titles" (obsolete table which has been dropped).
*** Added "DROP PROCEDURE" line for easier maintenance.
*** Created om database (none of the procs had been ported from L48 to Rizzo).
==SQL==
==SQL==
<section begin=sql /><mysql> CREATE PROCEDURE Upd_Titles_fr_Depts()
<section begin=sql /><mysql>DROP PROCEDURE IF EXISTS Upd_Titles_fr_Depts;
    REPLACE INTO _titles(ID,ID_Supp,CatNum,CatWeb)
CREATE PROCEDURE Upd_Titles_fr_Depts()
    SELECT
  REPLACE INTO _titles(ID,ID_Supp,CatNum,CatWeb)
      t.ID,
  SELECT
      d.ID_Supp,
    t.ID,
      UPPER(CONCAT_WS("-",d.CatNum,t.CatKey)) AS CatNum,
    d.ID_Supp,
      LOWER(CONCAT_WS("/",d.CatWeb_Title,t.CatKey)) AS CatWeb
    UPPER(CONCAT_WS("-",d.CatNum,t.CatKey)) AS CatNum,
    FROM titles AS t LEFT JOIN _depts AS d ON t.ID_dept=d.ID
    LOWER(CONCAT_WS("/",d.CatWeb_Title,t.CatKey)) AS CatWeb
    ORDER BY CatNum;</mysql>
  FROM cat_titles AS t LEFT JOIN _depts AS d ON t.ID_dept=d.ID
  ORDER BY CatNum;</mysql>
<section end=sql />
<section end=sql />

Revision as of 12:24, 10 November 2010

About

  • Input: Template:Vbzcart, Template:Vbzcart
  • Output: Template:Vbzcart
  • History:
    • 2010-11-10
      • Revised SQL to use "cat_titles" instead of "titles" (obsolete table which has been dropped).
      • Added "DROP PROCEDURE" line for easier maintenance.
      • Created om database (none of the procs had been ported from L48 to Rizzo).

SQL

<section begin=sql /><mysql>DROP PROCEDURE IF EXISTS Upd_Titles_fr_Depts; CREATE PROCEDURE Upd_Titles_fr_Depts() 

 REPLACE INTO _titles(ID,ID_Supp,CatNum,CatWeb)
 SELECT
   t.ID,
   d.ID_Supp,
   UPPER(CONCAT_WS("-",d.CatNum,t.CatKey)) AS CatNum,
   LOWER(CONCAT_WS("/",d.CatWeb_Title,t.CatKey)) AS CatWeb
 FROM cat_titles AS t LEFT JOIN _depts AS d ON t.ID_dept=d.ID
 ORDER BY CatNum;</mysql>

<section end=sql />

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