VbzCart/queries/qryCtg src - Revision history

Woozle: updated status

2016-03-02T22:46:45Z

updated status

← Older revision		Revision as of 22:46, 2 March 2016
Line 1:		Line 1:
	==About==		==About==
	* '''Requires''': {{~~vbzcart~~/query\|qryCtg_src_sub}}, {{~~vbzcart~~/query\|qryCat_Titles}}		* '''Requires''': {{l/vc/query\|qryCtg_src_sub}}, {{l/vc/query\|qryCat_Titles}}
	* '''Used by''': [[VbzCart catalog building\|catalog building process]] (details to be documented)		* '''Used by''': [[VbzCart catalog building\|catalog building process]] (details to be documented)
	* '''History''':		* '''History''':
Line 7:		Line 7:
	** '''2010-12-29''' Added t.ID_Supplier because {{vbzcart\|table\|cat_items}} now needs that field set.		** '''2010-12-29''' Added t.ID_Supplier because {{vbzcart\|table\|cat_items}} now needs that field set.
	** '''2011-01-02''' Added t.Name (AS TitleName) for the same reason		** '''2011-01-02''' Added t.Name (AS TitleName) for the same reason
			** '''2016-03-02''' This query no longer works, and also shouldn't be needed.
	* '''Fields''':		* '''Fields''':
	** '''b.ItOpt_Descr_part''' apparently often comes up as "" rather than NULL, so we have to convert blanks to nulls before testing for null.		** '''b.ItOpt_Descr_part''' apparently often comes up as "" rather than NULL, so we have to convert blanks to nulls before testing for null.
	==SQL==		==SQL==
	~~<section begin=sql />~~<mysql>CREATE OR REPLACE VIEW qryCtg_src AS		<mysql>CREATE OR REPLACE VIEW qryCtg_src AS
	SELECT		SELECT
	b.*,		b.*,
Line 19:		Line 20:
	IFNULL(NULLIF(b.ItOpt_Descr_part,""),b.NameSng) AS ItOpt_Descr		IFNULL(NULLIF(b.ItOpt_Descr_part,""),b.NameSng) AS ItOpt_Descr
	FROM qryCtg_src_sub AS b LEFT JOIN qryCat_Titles AS t ON b.ID_Title=t.ID;</mysql>		FROM qryCtg_src_sub AS b LEFT JOIN qryCat_Titles AS t ON b.ID_Title=t.ID;</mysql>
	~~<section end=sql />~~

Woozle: t.Name AS TitleName

2011-01-03T00:44:17Z

t.Name AS TitleName

← Older revision		Revision as of 00:44, 3 January 2011
Line 5:		Line 5:
	** '''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
	** '''2008-04-02''' convert blanks to nulls in b.ItOpt_Descr_part		** '''2008-04-02''' convert blanks to nulls in b.ItOpt_Descr_part
	** '''2010-12-29''' Added ID_Supplier because {{vbzcart\|table\|cat_items}} now needs that field set.		** '''2010-12-29''' Added t.ID_Supplier because {{vbzcart\|table\|cat_items}} now needs that field set.
			** '''2011-01-02''' Added t.Name (AS TitleName) for the same reason
	* '''Fields''':		* '''Fields''':
	** '''b.ItOpt_Descr_part''' apparently often comes up as "" rather than NULL, so we have to convert blanks to nulls before testing for null.		** '''b.ItOpt_Descr_part''' apparently often comes up as "" rather than NULL, so we have to convert blanks to nulls before testing for null.
Line 13:		Line 14:
	b.*,		b.*,
	t.ID_Supplier,		t.ID_Supplier,
			t.Name AS TitleName,
	CONCAT_WS('-',b.ID_Title,b.CatSfx) AS IDS_Item,		CONCAT_WS('-',b.ID_Title,b.CatSfx) AS IDS_Item,
	CONCAT_WS('-',t.CatNum,b.CatSfx) AS CatNum,		CONCAT_WS('-',t.CatNum,b.CatSfx) AS CatNum,

Woozle: +ID_Supplier

2010-12-30T02:52:42Z

+ID_Supplier

← Older revision		Revision as of 02:52, 30 December 2010
Line 5:		Line 5:
	** '''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
	** '''2008-04-02''' convert blanks to nulls in b.ItOpt_Descr_part		** '''2008-04-02''' convert blanks to nulls in b.ItOpt_Descr_part
			** '''2010-12-29''' Added ID_Supplier because {{vbzcart\|table\|cat_items}} now needs that field set.
	* '''Fields''':		* '''Fields''':
	** '''b.ItOpt_Descr_part''' apparently often comes up as "" rather than NULL, so we have to convert blanks to nulls before testing for null.		** '''b.ItOpt_Descr_part''' apparently often comes up as "" rather than NULL, so we have to convert blanks to nulls before testing for null.
Line 11:		Line 12:
	SELECT		SELECT
	b.*,		b.*,
			t.ID_Supplier,
	CONCAT_WS('-',b.ID_Title,b.CatSfx) AS IDS_Item,		CONCAT_WS('-',b.ID_Title,b.CatSfx) AS IDS_Item,
	CONCAT_WS('-',t.CatNum,b.CatSfx) AS CatNum,		CONCAT_WS('-',t.CatNum,b.CatSfx) AS CatNum,

Woozle: 2008-03-15

2009-05-03T22:19:23Z

2008-03-15

← Older revision		Revision as of 22:19, 3 May 2009
Line 3:		Line 3:
	* '''Used by''': [[VbzCart catalog building\|catalog building process]] (details to be documented)		* '''Used by''': [[VbzCart catalog building\|catalog building process]] (details to be documented)
	* '''History''':		* '''History''':
			** '''2008-03-15''' Building sequence completely rewritten, so presumably this query was created then
	** '''2008-04-02''' convert blanks to nulls in b.ItOpt_Descr_part		** '''2008-04-02''' convert blanks to nulls in b.ItOpt_Descr_part
	* '''Fields''':		* '''Fields''':

Woozle: extracted from catalog building page

2009-05-03T21:51:33Z

extracted from catalog building page

New page

==About==
* '''Requires''': {{vbzcart/query|qryCtg_src_sub}}, {{vbzcart/query|qryCat_Titles}}
* '''Used by''': [[VbzCart catalog building|catalog building process]] (details to be documented)
* '''History''':
** '''2008-04-02''' convert blanks to nulls in b.ItOpt_Descr_part
* '''Fields''':
** '''b.ItOpt_Descr_part''' apparently often comes up as "" rather than NULL, so we have to convert blanks to nulls before testing for null.
==SQL==
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCtg_src AS
SELECT
b.*,
CONCAT_WS('-',b.ID_Title,b.CatSfx) AS IDS_Item,
CONCAT_WS('-',t.CatNum,b.CatSfx) AS CatNum,
IFNULL(NULLIF(b.ItOpt_Descr_part,""),b.NameSng) AS ItOpt_Descr
FROM qryCtg_src_sub AS b LEFT JOIN qryCat_Titles AS t ON b.ID_Title=t.ID;</mysql>
<section end=sql />