Difference between revisions of "VbzCart/queries/qryCat Items"
Jump to navigation
Jump to search
(moved from single-page listing) |
(filter out pulled/dumped items) |
||
| Line 4: | Line 4: | ||
* '''History''': | * '''History''': | ||
** '''2008-11-22''' Added '''isInPrint''' field so we could use this in [[../qryItms_to_restock_w_info|qryItms_to_restock_w_info]] | ** '''2008-11-22''' Added '''isInPrint''' field so we could use this in [[../qryItms_to_restock_w_info|qryItms_to_restock_w_info]] | ||
| + | ** '''2010-11-06''' Added WHERE clause to remove Pulled and Dumped items, because their catalog information (which is what this query is about) should be irrelevant. Removed isPulled field from results because it will always be FALSE. | ||
* '''MySQL note''': Functions (or [[MySQL/CONCAT|CONCAT]], anyway) apparently do not like being separated from their opening parenthesis; it seems to resolve the function name as a field name, and (generally) will report that there is no such field. | * '''MySQL note''': Functions (or [[MySQL/CONCAT|CONCAT]], anyway) apparently do not like being separated from their opening parenthesis; it seems to resolve the function name as a field name, and (generally) will report that there is no such field. | ||
==SQL== | ==SQL== | ||
| Line 31: | Line 32: | ||
i.isForSale, | i.isForSale, | ||
i.isInPrint, | i.isInPrint, | ||
| − | |||
i.QtyMin_Stk, | i.QtyMin_Stk, | ||
t.QtyMin_Rstk AS QtyMin_Rstk_Title | t.QtyMin_Rstk AS QtyMin_Rstk_Title | ||
FROM cat_items AS i LEFT JOIN qryCat_Titles AS t on i.ID_Title=t.ID | FROM cat_items AS i LEFT JOIN qryCat_Titles AS t on i.ID_Title=t.ID | ||
| + | WHERE (NOT isPulled) AND (NOT isDumped) | ||
ORDER BY t.ID IS NOT NULL, i.CatNum, i.ItOpt_Sort;</mysql> | ORDER BY t.ID IS NOT NULL, i.CatNum, i.ItOpt_Sort;</mysql> | ||
<section end=sql /> | <section end=sql /> | ||
Revision as of 14:19, 6 November 2010
Details
- Requires: cat_items, qryCat_Titles
- Used by: qryItms_to_restock_w_info and others
- History:
- 2008-11-22 Added isInPrint field so we could use this in qryItms_to_restock_w_info
- 2010-11-06 Added WHERE clause to remove Pulled and Dumped items, because their catalog information (which is what this query is about) should be irrelevant. Removed isPulled field from results because it will always be FALSE.
- MySQL note: Functions (or CONCAT, anyway) apparently do not like being separated from their opening parenthesis; it seems to resolve the function name as a field name, and (generally) will report that there is no such field.
SQL
<mysql>CREATE OR REPLACE VIEW qryCat_Items AS SELECT
i.ID,
i.ID_Title,
(t.ID IS NOT NULL) AS TitleExists,
t.ID_Supplier,
i.PriceSell,
i.PriceList,
i.PriceBuy,
i.CatNum,
t.CatNum AS Title_CatNum,
t.Name AS Title_Name,
i.ID_ItTyp,
i.ID_ItOpt,
i.ItOpt_Descr,
i.ItOpt_Sort,
CONCAT(t.Name, IF
(i.ItOpt_Descr IS NULL,,CONCAT(
' (',i.ItOpt_Descr,')'
)
)
) AS Descr,
i.Supp_CatNum,
i.isForSale,
i.isInPrint,
i.QtyMin_Stk,
t.QtyMin_Rstk AS QtyMin_Rstk_Title
FROM cat_items AS i LEFT JOIN qryCat_Titles AS t on i.ID_Title=t.ID WHERE (NOT isPulled) AND (NOT isDumped) ORDER BY t.ID IS NOT NULL, i.CatNum, i.ItOpt_Sort;</mysql>