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

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
(fixed obsoleteness)
m (removed obsolete tag)
 
(One intermediate revision by the same user not shown)
Line 6: Line 6:
 
*** Revised SQL to use "cat_titles" instead of "titles" (obsolete table which has been dropped).
 
*** Revised SQL to use "cat_titles" instead of "titles" (obsolete table which has been dropped).
 
*** Added "DROP PROCEDURE" line for easier maintenance.
 
*** Added "DROP PROCEDURE" line for easier maintenance.
*** Created om database (none of the procs had been ported from L48 to Rizzo).
+
*** Created in database (none of the procs had been ported from L48 to Rizzo).
 +
 
 
==SQL==
 
==SQL==
<section begin=sql /><mysql>DROP PROCEDURE IF EXISTS Upd_Titles_fr_Depts;
+
<mysql>DROP PROCEDURE IF EXISTS Upd_Titles_fr_Depts;
 
CREATE PROCEDURE Upd_Titles_fr_Depts()
 
CREATE PROCEDURE Upd_Titles_fr_Depts()
 
   REPLACE INTO _titles(ID,ID_Supp,CatNum,CatWeb)
 
   REPLACE INTO _titles(ID,ID_Supp,CatNum,CatWeb)
Line 18: Line 19:
 
   FROM cat_titles AS t LEFT JOIN _depts AS d ON t.ID_dept=d.ID
 
   FROM cat_titles AS t LEFT JOIN _depts AS d ON t.ID_dept=d.ID
 
   ORDER BY CatNum;</mysql>
 
   ORDER BY CatNum;</mysql>
<section end=sql />
 

Latest revision as of 01:21, 7 September 2015

About

  • Input: cat_titles, _depts
  • Output: _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 in database (none of the procs had been ported from L48 to Rizzo).

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>