VbzCart/queries/qryCat Items - Revision history

Woozle: status update

2016-03-02T22:04:17Z

status update

← Older revision		Revision as of 22:04, 2 March 2016
Line 6:		Line 6:
	** '''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.		** '''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.
	** '''2011-12-24''' Apparently NOT NULL evaluates to FALSE (either this is new or an unnoticed bug), so if we want NULL values of isDumped and isPulled to count as FALSE, we have to say IFNULL(isPulled,0)=0 etc.		** '''2011-12-24''' Apparently NOT NULL evaluates to FALSE (either this is new or an unnoticed bug), so if we want NULL values of isDumped and isPulled to count as FALSE, we have to say IFNULL(isPulled,0)=0 etc.
			** '''2016-03-02''' Discovered that this query doesn't even work anymore, and therefore is not in use (which is as it should be). Moved to "discarded" list.
	* '''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==
	~~<section begin=sql />~~<mysql>CREATE OR REPLACE VIEW qryCat_Items AS		<mysql>CREATE OR REPLACE VIEW qryCat_Items AS
	SELECT		SELECT
	i.ID,		i.ID,
Line 38:		Line 39:
	WHERE (IFNULL(isPulled,0)=0) AND (IFNULL(isDumped,0)=0)		WHERE (IFNULL(isPulled,0)=0) AND (IFNULL(isDumped,0)=0)
	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 />~~

Woozle: fixed flag-state detection bug (when flag value is NULL)

2011-12-25T03:41:44Z

fixed flag-state detection bug (when flag value is NULL)

← Older revision		Revision as of 03:41, 25 December 2011
Line 5:		Line 5:
	** '''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.		** '''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.
			** '''2011-12-24''' Apparently NOT NULL evaluates to FALSE (either this is new or an unnoticed bug), so if we want NULL values of isDumped and isPulled to count as FALSE, we have to say IFNULL(isPulled,0)=0 etc.
	* '''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 35:		Line 36:
	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)		WHERE (IFNULL(isPulled,0)=0) AND (IFNULL(isDumped,0)=0)
	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 />

Woozle: filter out pulled/dumped items

2010-11-06T14:19:36Z

filter out pulled/dumped items

← Older revision		Revision as of 14:19, 6 November 2010
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.isPulled,~~
	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 />

Woozle: moved from single-page listing

2008-11-30T19:01:11Z

moved from single-page listing

New page

==Details==
* '''Requires''': [[../../tables/cat_items|cat_items]], [[../qryCat_Titles|qryCat_Titles]]
* '''Used by''': [[../qryItms_to_restock_w_info|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|qryItms_to_restock_w_info]]
* '''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==
<section begin=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.isPulled,
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
ORDER BY t.ID IS NOT NULL, i.CatNum, i.ItOpt_Sort;</mysql>
<section end=sql />