VbzCart/queries/qryCtg src sub - Revision history

Woozle: status update

2016-03-02T22:42:31Z

status update

← Older revision		Revision as of 22:42, 2 March 2016
Line 8:		Line 8:
	** '''2009-05-03''' (dev note) Moved doc to separate page; added query to main server (no design change)		** '''2009-05-03''' (dev note) Moved doc to separate page; added query to main server (no design change)
	** '''2014-08-17''' Added DISTINCTROW and removed '''gt.ID AS ID_CTG_Title''' and '''gt.ID_Group AS ID_CTG_Group''' to eliminate duplicate entries in ctg_upd1 when same title belongs to multiple groups		** '''2014-08-17''' Added DISTINCTROW and removed '''gt.ID AS ID_CTG_Title''' and '''gt.ID_Group AS ID_CTG_Group''' to eliminate duplicate entries in ctg_upd1 when same title belongs to multiple groups
			** '''2016-03-02''' This query no longer works, and shouldn't be needed anyway.
	==SQL==		==SQL==
	<mysql>CREATE OR REPLACE VIEW qryCtg_src_sub AS		<mysql>CREATE OR REPLACE VIEW qryCtg_src_sub AS

Woozle: eliminate duplicate rows

2014-08-18T01:03:17Z

eliminate duplicate rows

← Older revision		Revision as of 01:03, 18 August 2014
Line 3:		Line 3:
	** '''Queries''': {{vbzcart/query\|qryCtg_Titles_active}}, {{vbzcart/query\|qryCtg_Sources_active}}, {{vbzcart/query\|qryCtg_Items_active}}		** '''Queries''': {{vbzcart/query\|qryCtg_Titles_active}}, {{vbzcart/query\|qryCtg_Sources_active}}, {{vbzcart/query\|qryCtg_Items_active}}
	** '''Tables''': {{vbzcart\|table\|ctg_groups}}, {{vbzcart\|table\|cat_ioptns}}, {{vbzcart\|table\|cat_ittyps}}		** '''Tables''': {{vbzcart\|table\|ctg_groups}}, {{vbzcart\|table\|cat_ioptns}}, {{vbzcart\|table\|cat_ittyps}}
	* '''Used by''': [[VbzCart catalog building\|catalog building process]] ~~(details to be documented)~~		* '''Used by''': [[VbzCart/catalog/building\|catalog building process]], which saves output in {{vbzcart\|table\|ctg_upd1}}
	* '''History''':		* '''History''':
	** '''2008-03-15''' Building sequence completely rewritten, so presumably this query was created then		** '''2008-03-15''' Building sequence completely rewritten, so presumably this query was created then
	** '''2009-05-03''' (dev note) Moved doc to separate page; added query to main server (no design change)		** '''2009-05-03''' (dev note) Moved doc to separate page; added query to main server (no design change)
			** '''2014-08-17''' Added DISTINCTROW and removed '''gt.ID AS ID_CTG_Title''' and '''gt.ID_Group AS ID_CTG_Group''' to eliminate duplicate entries in ctg_upd1 when same title belongs to multiple groups
	==SQL==		==SQL==
	~~<section begin=sql />~~<mysql>CREATE OR REPLACE VIEW qryCtg_src_sub AS		<mysql>CREATE OR REPLACE VIEW qryCtg_src_sub AS
	SELECT		SELECT DISTINCT
	NULLIF(CONCAT_WS('/',g.Code,gt.GroupCode,o.CatKey),'') AS CatSfx,		NULLIF(CONCAT_WS('/',g.Code,gt.GroupCode,o.CatKey),'') AS CatSfx,
	c.isCloseOut,		c.isCloseOut,
	~~gt.ID AS ID_CTG_Title,~~
	gi.ID AS ID_CTG_Item,		gi.ID AS ID_CTG_Item,
	~~gt.ID_Group AS ID_CTG_Group, /* mainly for debugging */~~
	gt.ID_Source AS ID_CTG_Source, /* mainly for debugging */		gt.ID_Source AS ID_CTG_Source, /* mainly for debugging */
	gt.ID_Title,		gt.ID_Title,
Line 43:		Line 42:
	) LEFT JOIN cat_ittyps AS it ON gi.ID_ItTyp = it.ID		) LEFT JOIN cat_ittyps AS it ON gi.ID_ItTyp = it.ID
	WHERE (c.ID IS NOT NULL) AND (gi.ID IS NOT NULL);</mysql>		WHERE (c.ID IS NOT NULL) AND (gi.ID IS NOT NULL);</mysql>
	~~<section end=sql />~~

Woozle: 2008-03-15

2009-05-03T22:20:01Z

2008-03-15

← Older revision		Revision as of 22:20, 3 May 2009
Line 5:		Line 5:
	* '''Used by''': [[VbzCart catalog building\|catalog building process]] (details to be documented)		* '''Used by''': [[VbzCart catalog building\|catalog building process]] (details to be documented)
	* '''History''':		* '''History''':
			** '''2008-03-15''' Building sequence completely rewritten, so presumably this query was created then
	** '''2009-05-03''' (dev note) Moved doc to separate page; added query to main server (no design change)		** '''2009-05-03''' (dev note) Moved doc to separate page; added query to main server (no design change)

	==SQL==		==SQL==
	<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCtg_src_sub AS		<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCtg_src_sub AS

Woozle: /* About */ source clarification

2009-05-03T21:51:06Z

About: source clarification

← Older revision		Revision as of 21:51, 3 May 2009
Line 3:		Line 3:
	** '''Queries''': {{vbzcart/query\|qryCtg_Titles_active}}, {{vbzcart/query\|qryCtg_Sources_active}}, {{vbzcart/query\|qryCtg_Items_active}}		** '''Queries''': {{vbzcart/query\|qryCtg_Titles_active}}, {{vbzcart/query\|qryCtg_Sources_active}}, {{vbzcart/query\|qryCtg_Items_active}}
	** '''Tables''': {{vbzcart\|table\|ctg_groups}}, {{vbzcart\|table\|cat_ioptns}}, {{vbzcart\|table\|cat_ittyps}}		** '''Tables''': {{vbzcart\|table\|ctg_groups}}, {{vbzcart\|table\|cat_ioptns}}, {{vbzcart\|table\|cat_ittyps}}
	* '''Used by''': catalog building process~~, supposedly - but where specifically?~~		* '''Used by''': [[VbzCart catalog building\|catalog building process]] (details to be documented)
	* '''History''':		* '''History''':
	** '''2009-05-03''' (dev note) Moved doc to separate page; added query to main server (no design change)		** '''2009-05-03''' (dev note) Moved doc to separate page; added query to main server (no design change)

	==SQL==		==SQL==
	<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCtg_src_sub AS		<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCtg_src_sub AS

Woozle: extracted from catalog building page

2009-05-03T21:46:36Z

extracted from catalog building page

New page

==About==
* '''Requires''':
** '''Queries''': {{vbzcart/query|qryCtg_Titles_active}}, {{vbzcart/query|qryCtg_Sources_active}}, {{vbzcart/query|qryCtg_Items_active}}
** '''Tables''': {{vbzcart|table|ctg_groups}}, {{vbzcart|table|cat_ioptns}}, {{vbzcart|table|cat_ittyps}}
* '''Used by''': catalog building process, supposedly - but where specifically?
* '''History''':
** '''2009-05-03''' (dev note) Moved doc to separate page; added query to main server (no design change)
==SQL==
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCtg_src_sub AS
SELECT
NULLIF(CONCAT_WS('/',g.Code,gt.GroupCode,o.CatKey),'') AS CatSfx,
c.isCloseOut,
gt.ID AS ID_CTG_Title,
gi.ID AS ID_CTG_Item,
gt.ID_Group AS ID_CTG_Group, /* mainly for debugging */
gt.ID_Source AS ID_CTG_Source, /* mainly for debugging */
gt.ID_Title,
gi.ID_ItTyp,
gi.ID_ItOpt,
gi.ID_ShipCost,
gi.PriceBuy,
gi.PriceSell,
gi.PriceList,
IFNULL(gi.Descr,CONCAT_WS(' / ',gt.GroupDescr,o.Descr)) AS ItOpt_Descr_part,
it.NameSng,
gi.Descr AS GrpItmDescr,
gt.GroupDescr AS TitleGroupDescr,
o.Descr AS OptionDescr,
CONCAT(g.Sort,it.Sort,o.Sort) AS ItOpt_Sort,
gt.GroupCode AS GrpCode,
gt.GroupDescr AS GrpDescr,
gt.GroupSort AS GrpSort
FROM
(
(
(
(
qryCtg_Titles_active AS gt LEFT JOIN ctg_groups AS g ON g.ID=gt.ID_Group
) LEFT JOIN qryCtg_Sources_active AS c ON gt.ID_Source=c.ID
) LEFT JOIN qryCtg_Items_active AS gi ON gi.ID_Group=g.ID
) LEFT JOIN cat_ioptns AS o ON gi.ID_ItOpt=o.ID
) LEFT JOIN cat_ittyps AS it ON gi.ID_ItTyp = it.ID
WHERE (c.ID IS NOT NULL) AND (gi.ID IS NOT NULL);</mysql>
<section end=sql />