VbzCart/procs/Upd Depts fr Depts Suppliers - Revision history

Woozle: made it go

2010-11-10T12:18:45Z

made it go

← Older revision		Revision as of 12:18, 10 November 2010
Line 3:		Line 3:
	* '''Input''': {{vbzcart\|table\|cat_depts}}, {{vbzcart\|table\|cat_supp}}		* '''Input''': {{vbzcart\|table\|cat_depts}}, {{vbzcart\|table\|cat_supp}}
	* '''Output''': {{vbzcart\|table\|_depts}} (delete/insert)		* '''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`//
			CREATE PROCEDURE Upd_Depts_fr_Depts_Suppliers()
			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`)
Line 16:		Line 20:
	LOWER(CONCAT_WS("/",s.CatKey,d.CatKey)) AS CatWeb_Title		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;		FROM cat_depts AS d LEFT JOIN cat_supp AS s ON d.ID_Supplier=s.ID;
	END</mysql>		END//
			DELIMITER ;</mysql>
	<section end=sql />		<section end=sql />

Woozle: updated source table names; more doc

2009-03-08T21:20:10Z

updated source table names; more doc

← Older revision		Revision as of 21:20, 8 March 2009
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 />

Woozle: New page: ==About== * '''Purpose''': fills {{vbzcart|table|_depts}} (does not set inPrint/inStock fields) ==SQL==
CREATE PROCEDURE Upd_Depts_fr_Depts_Suppliers() BE...

2009-03-08T19:31:49Z

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

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()
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 depts AS d LEFT JOIN suppliers AS s ON d.ID_Supplier=s.ID;
END</mysql>
<section end=sql />

VbzCart/procs/Upd Depts fr Depts Suppliers - Revision history

Woozle: made it go

Woozle: updated source table names; more doc

Woozle: New page: ==About== * '''Purpose''': fills {{vbzcart|table|_depts}} (does not set inPrint/inStock fields) ==SQL== CREATE PROCEDURE Upd_Depts_fr_Depts_Suppliers() BE...

Woozle: New page: ==About== * '''Purpose''': fills {{vbzcart|table|_depts}} (does not set inPrint/inStock fields) ==SQL==
CREATE PROCEDURE Upd_Depts_fr_Depts_Suppliers() BE...