Difference between revisions of "VbzCart/queries/qryCtg Upd join"
Jump to navigation
Jump to search
m (2008-03-15) |
(*this* is where Descr gets calculated, otherwise you get problems) |
||
| (3 intermediate revisions by the same user not shown) | |||
| 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 | ||
| + | ** '''2010-12-29''' Added '''ID_Supp''' field because {{{vbzcart|table|cat_items}} needs it | ||
| + | ** '''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 14: | Line 17: | ||
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 */ | ||
| − | |||
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, | ||
| + | s.ID_Supp, | ||
s.ID_Title, | s.ID_Title, | ||
s.ID_ItTyp, | s.ID_ItTyp, | ||
s.ID_ItOpt, | s.ID_ItOpt, | ||
| + | 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, | ||
| Line 33: | Line 38: | ||
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 /> | ||
Latest revision as of 01:59, 3 January 2011
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 cat_items EXCEPT Supp_CatNum and QtyMin_Stock. (Maybe these should later be moved to a separate table.)
- Requires: ctg_upd1, ctg_upd2
- Used by: 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?
- History:
- 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
- 2010-12-29 Added ID_Supp field because {cat_items needs it
- 2011-01-02 Added Descr field for the same reason
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 */
NOT s.isCloseOut AS isInPrint,
s.isCloseOut,
NULL AS isCurrent, /* figure out later how to calculate this */
FALSE AS isPulled,
FALSE AS isDumped,
s.ID_Supp,
s.ID_Title,
s.ID_ItTyp,
s.ID_ItOpt,
CONCAT_WS(" - ",s.TitleName,s.NameSng,s.GrpDescr,s.ItOpt_Descr) AS Descr,
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.qtyInStock AS QtyIn_Stk, /* this has just been recalculated from stock */
c.QtyMin_Stk
FROM ctg_upd1 AS s LEFT JOIN ctg_upd2 AS c ON s.IDS_Item=c.IDS_Item;</mysql>