VbzCart/queries/qryCtg Upd join - Revision history

Woozle: this is where Descr gets calculated, otherwise you get problems

2011-01-03T01:59:25Z

*this* is where Descr gets calculated, otherwise you get problems

← Older revision		Revision as of 01:59, 3 January 2011
Line 9:		Line 9:
	** '''2009-12-11''' Renamed "qtyInStock" to "QtyIn_Stk" and moved it; added "isCurrent", but set it to NULL until we can figure out how to calculate it		** '''2009-12-11''' Renamed "qtyInStock" to "QtyIn_Stk" and moved it; added "isCurrent", but set it to NULL until we can figure out how to calculate it
	** '''2010-12-29''' Added '''ID_Supp''' field because {{{vbzcart\|table\|cat_items}} needs it		** '''2010-12-29''' Added '''ID_Supp''' field because {{{vbzcart\|table\|cat_items}} needs it
	** '''2011-01-02''' Added '''Descr''' field for the same reason ~~(is set to NULL here; filled in by a later step)~~		** '''2011-01-02''' Added '''Descr''' field for the same reason
	==SQL==		==SQL==
	<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCtg_Upd_join AS		<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCtg_Upd_join AS
Line 26:		Line 26:
	s.ID_ItTyp,		s.ID_ItTyp,
	s.ID_ItOpt,		s.ID_ItOpt,
	~~NULL~~ AS Descr,		CONCAT_WS(" - ",s.TitleName,s.NameSng,s.GrpDescr,s.ItOpt_Descr) AS Descr,
	s.ItOpt_Descr,		s.ItOpt_Descr,
	s.ItOpt_Sort,		s.ItOpt_Sort,

Woozle: Descr

2011-01-03T00:23:34Z

Descr

← Older revision		Revision as of 00:23, 3 January 2011
Line 9:		Line 9:
	** '''2009-12-11''' Renamed "qtyInStock" to "QtyIn_Stk" and moved it; added "isCurrent", but set it to NULL until we can figure out how to calculate it		** '''2009-12-11''' Renamed "qtyInStock" to "QtyIn_Stk" and moved it; added "isCurrent", but set it to NULL until we can figure out how to calculate it
	** '''2010-12-29''' Added '''ID_Supp''' field because {{{vbzcart\|table\|cat_items}} needs it		** '''2010-12-29''' Added '''ID_Supp''' field because {{{vbzcart\|table\|cat_items}} needs it
			** '''2011-01-02''' Added '''Descr''' field for the same reason (is set to NULL here; filled in by a later step)
	==SQL==		==SQL==
	<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCtg_Upd_join AS		<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCtg_Upd_join AS
Line 25:		Line 26:
	s.ID_ItTyp,		s.ID_ItTyp,
	s.ID_ItOpt,		s.ID_ItOpt,
			NULL AS Descr,
	s.ItOpt_Descr,		s.ItOpt_Descr,
	s.ItOpt_Sort,		s.ItOpt_Sort,

Woozle: +ID_Supp

2010-12-30T02:59:25Z

+ID_Supp

← Older revision		Revision as of 02:59, 30 December 2010
Line 8:		Line 8:
	** '''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-12-11''' Renamed "qtyInStock" to "QtyIn_Stk" and moved it; added "isCurrent", but set it to NULL until we can figure out how to calculate it		** '''2009-12-11''' Renamed "qtyInStock" to "QtyIn_Stk" and moved it; added "isCurrent", but set it to NULL until we can figure out how to calculate it
			** '''2010-12-29''' Added '''ID_Supp''' field because {{{vbzcart\|table\|cat_items}} needs it
	==SQL==		==SQL==
	<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCtg_Upd_join AS		<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCtg_Upd_join AS
Line 20:		Line 21:
	FALSE AS isPulled,		FALSE AS isPulled,
	FALSE AS isDumped,		FALSE AS isDumped,
			s.ID_Supp,
	s.ID_Title,		s.ID_Title,
	s.ID_ItTyp,		s.ID_ItTyp,

Woozle: QtyIn_Stk, isCurrent

2009-12-13T14:30:36Z

QtyIn_Stk, isCurrent

← Older revision		Revision as of 14:30, 13 December 2009
Line 7:		Line 7:
	* '''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-12-11''' Renamed "qtyInStock" to "QtyIn_Stk" and moved it; added "isCurrent", but set it to NULL until we can figure out how to calculate it
	==SQL==		==SQL==
	<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCtg_Upd_join AS		<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCtg_Upd_join AS
Line 14:		Line 15:
	TRUE AS isForSale, /* has catalog source, so is for sale */		TRUE AS isForSale, /* has catalog source, so is for sale */
	FALSE AS isMaster, /* master items are entered manually */		FALSE AS isMaster, /* master items are entered manually */
	~~c.qtyInStock, /* this has just been recalculated from stock */~~
	NOT s.isCloseOut AS isInPrint,		NOT s.isCloseOut AS isInPrint,
	s.isCloseOut,		s.isCloseOut,
			NULL AS isCurrent, /* figure out later how to calculate this */
	FALSE AS isPulled,		FALSE AS isPulled,
	FALSE AS isDumped,		FALSE AS isDumped,
Line 33:		Line 34:
	s.PriceList,		s.PriceList,
	c.Supp_CatNum,		c.Supp_CatNum,
			c.qtyInStock AS QtyIn_Stk, /* this has just been recalculated from stock */
	c.QtyMin_Stk		c.QtyMin_Stk
	FROM ctg_upd1 AS s LEFT JOIN ctg_upd2 AS c ON s.IDS_Item=c.IDS_Item;</mysql>		FROM ctg_upd1 AS s LEFT JOIN ctg_upd2 AS c ON s.IDS_Item=c.IDS_Item;</mysql>
	<section end=sql />		<section end=sql />

Woozle: 2008-03-15

2009-05-03T22:21:27Z

2008-03-15

← Older revision		Revision as of 22:21, 3 May 2009
Line 5:		Line 5:
	* '''Fields''':		* '''Fields''':
	** '''s.CatNum''' -- the CTG CatNum -- should never be NULL; this indicates something is wrong in the sourcing process, because how can you have an active source with no title CatNum?		** '''s.CatNum''' -- the CTG CatNum -- should never be NULL; this indicates something is wrong in the sourcing process, because how can you have an active source with no title CatNum?
			* '''History''':
			** '''2008-03-15''' Building sequence completely rewritten, so presumably this query was created then
	==SQL==		==SQL==
	<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCtg_Upd_join AS		<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCtg_Upd_join AS

Woozle: extracted from catalog building page

2009-05-03T21:58:05Z

extracted from catalog building page

New page

==About==
* '''Purpose''': This recordset represents all the catalog items for which there is currently an active source (in print or closeout, but not stock). It should not need to preserve anything from the current record in {{vbzcart|table|cat_items}} EXCEPT Supp_CatNum and QtyMin_Stock. (Maybe these should later be moved to a separate table.)
* '''Requires''': {{vbzcart|table|ctg_upd1}}, {{vbzcart|table|ctg_upd2}}
* '''Used by''': [[VbzCart catalog building|catalog building process]] (details to be documented)
* '''Fields''':
** '''s.CatNum''' -- the CTG CatNum -- should never be NULL; this indicates something is wrong in the sourcing process, because how can you have an active source with no title CatNum?
==SQL==
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCtg_Upd_join AS
SELECT
c.ID_Item AS ID,
s.CatNum AS CatNum,
TRUE AS isForSale, /* has catalog source, so is for sale */
FALSE AS isMaster, /* master items are entered manually */
c.qtyInStock, /* this has just been recalculated from stock */
NOT s.isCloseOut AS isInPrint,
s.isCloseOut,
FALSE AS isPulled,
FALSE AS isDumped,
s.ID_Title,
s.ID_ItTyp,
s.ID_ItOpt,
s.ItOpt_Descr,
s.ItOpt_Sort,
s.GrpCode,
s.GrpDescr,
s.GrpSort,
s.CatSfx,
s.ID_ShipCost,
s.PriceBuy,
s.PriceSell,
s.PriceList,
c.Supp_CatNum,
c.QtyMin_Stk
FROM ctg_upd1 AS s LEFT JOIN ctg_upd2 AS c ON s.IDS_Item=c.IDS_Item;</mysql>
<section end=sql />