Difference between revisions of "VbzCart/procs/Upd TitleIttyps fr CatItems Titles"
Jump to navigation
Jump to search
(fixed missing target field) |
(conditions to prevent records with null ID_Dept and ID_ItTyp) |
||
| Line 12: | Line 12: | ||
** '''2010-11-13''' Source field '''qtyInStock''' was renamed to '''qtyIn_Stk''' some time ago... | ** '''2010-11-13''' Source field '''qtyInStock''' was renamed to '''qtyIn_Stk''' some time ago... | ||
*** Added '''cntInPrint''', '''cntLines''' | *** Added '''cntInPrint''', '''cntLines''' | ||
| + | *** Added (ID_Dept IS NOT NULL) AND (ID_ItTyp IS NOT NULL) to prevent those fields being null (not allowed in target table) | ||
* '''Notes''': | * '''Notes''': | ||
** This is a revised version which combines two earlier procedures. | ** This is a revised version which combines two earlier procedures. | ||
| Line 57: | Line 58: | ||
FROM cat_items AS i | FROM cat_items AS i | ||
GROUP BY ID_Title, ID_ItTyp | GROUP BY ID_Title, ID_ItTyp | ||
| − | ) AS i LEFT JOIN cat_titles AS t ON i.ID_Title=t.ID;</mysql> | + | ) AS i LEFT JOIN cat_titles AS t ON i.ID_Title=t.ID |
| + | WHERE (ID_Dept IS NOT NULL) AND (ID_ItTyp IS NOT NULL);</mysql> | ||
<section end=sql /> | <section end=sql /> | ||
Revision as of 00:53, 14 November 2010
About
- Purpose: fills _title_ittyps.
- Input: cat_items, cat_titles
- Output: _title_ittyps (replace)
- History:
- 2007-09-20
- Removed "WHERE i.isForSale" because this prevented the availability of discontinued items from being properly zeroed out
- Added cntInStock calculation; see table schema
- 2010-11-10
- Added DROP PROCEDURE for easier maintenance.
- Actually created in database (procs did not get ported from L48 to Rizzo).
- 2010-11-13 Source field qtyInStock was renamed to qtyIn_Stk some time ago...
- Added cntInPrint, cntLines
- Added (ID_Dept IS NOT NULL) AND (ID_ItTyp IS NOT NULL) to prevent those fields being null (not allowed in target table)
- 2007-09-20
- Notes:
- This is a revised version which combines two earlier procedures.
- I had a note that "the ID_Dept field is informational, not a grouping", but I don't remember what that meant and it doesn't make sense.
SQL
<mysql>DROP PROCEDURE IF EXISTS Upd_TitleIttyps_fr_CatItems_Titles; CREATE PROCEDURE Upd_TitleIttyps_fr_CatItems_Titles()
REPLACE INTO _title_ittyps(
ID_Title,
ID_ItTyp,
ID_Dept,
TitleName,
cntLines,
cntCurrent,
cntInPrint,
cntForSale,
cntInStock,
qtyInStock,
currMinPrice,
currMaxPrice)
SELECT
i.ID_Title,
i.ID_ItTyp,
t.ID_Dept,
t.Name AS TitleName,
i.cntLines,
i.cntCurrent,
i.cntInPrint,
i.cntForSale,
i.cntInStock,
i.qtyInStock,
i.currMinPrice,
i.currMaxPrice
FROM (
SELECT
ID_Title, ID_ItTyp,
COUNT(ID) AS cntLines,
SUM(IF(isCurrent,1,0)) AS cntCurrent,
SUM(IF(isInPrint,1,0)) AS cntInPrint,
SUM(IF(isForSale,1,0)) AS cntForSale,
SUM(IF(qtyIn_Stk>0,1,0)) AS cntInStock,
SUM(qtyIn_Stk) AS qtyInStock,
MIN(PriceSell) AS currMinPrice,
MAX(PriceSell) AS currMaxPrice
FROM cat_items AS i
GROUP BY ID_Title, ID_ItTyp
) AS i LEFT JOIN cat_titles AS t ON i.ID_Title=t.ID
WHERE (ID_Dept IS NOT NULL) AND (ID_ItTyp IS NOT NULL);</mysql>