Difference between revisions of "VbzCart/queries"
(→qryCbx_Orders: sorting) |
(→Views: moved v_* views here from "tables" page) |
||
Line 4: | Line 4: | ||
"Views" in [[MySQL]] are like SELECT "queries" in [[MS Access]], i.e. they pull data from existing tables and are themselves usable as data sources in much the same way that tables are (and in which result sets from functions are not). | "Views" in [[MySQL]] are like SELECT "queries" in [[MS Access]], i.e. they pull data from existing tables and are themselves usable as data sources in much the same way that tables are (and in which result sets from functions are not). | ||
==Views== | ==Views== | ||
+ | "Views" in [[MySQL]] are essentially identical to stored queries in [[MS Access]]. | ||
+ | |||
Some common prefixes: | Some common prefixes: | ||
* '''qryCbx_''': queries used for filling comboboxes. The unique ID will always be the first field, and the text to display will always be the second field; additional fields may be provided for use in further '''qryCbx_''' queries which show a subset of the results. | * '''qryCbx_''': queries used for filling comboboxes. The unique ID will always be the first field, and the text to display will always be the second field; additional fields may be provided for use in further '''qryCbx_''' queries which show a subset of the results. | ||
Line 17: | Line 19: | ||
CONCAT_WS('-',s.CatKey,d.CatKey) AS CatNum | CONCAT_WS('-',s.CatKey,d.CatKey) AS CatNum | ||
FROM depts AS d LEFT JOIN suppliers AS s ON d.ID_Supplier=s.ID;</mysql> | FROM depts AS d LEFT JOIN suppliers AS s ON d.ID_Supplier=s.ID;</mysql> | ||
− | ===qryCat_Titles=== | + | ===Titles=== |
+ | These will later reference cat_titles instead of titles, when that table is fully migrated. | ||
+ | ====v_titles==== | ||
+ | <mysql>CREATE OR REPLACE VIEW v_titles AS | ||
+ | SELECT | ||
+ | t.*, | ||
+ | tx.ID_Supp, | ||
+ | tx.CatNum, | ||
+ | tx.CatWeb, | ||
+ | tx.cntForSale, | ||
+ | tx.cntInPrint, | ||
+ | tx.qtyInStock | ||
+ | FROM titles AS t LEFT JOIN _titles AS tx on t.ID=tx.ID;</mysql> | ||
+ | ====qryCat_Titles==== | ||
<mysql>CREATE OR REPLACE VIEW qryCat_Titles AS | <mysql>CREATE OR REPLACE VIEW qryCat_Titles AS | ||
SELECT | SELECT | ||
Line 30: | Line 45: | ||
t.Supplier_CatNum AS Supp_CatNum | t.Supplier_CatNum AS Supp_CatNum | ||
FROM titles AS t LEFT JOIN qryCat_Depts AS d ON t.ID_Dept=d.ID;</mysql> | FROM titles AS t LEFT JOIN qryCat_Depts AS d ON t.ID_Dept=d.ID;</mysql> | ||
− | ===qryCat_Items=== | + | ===Items=== |
+ | ====v_items==== | ||
+ | <mysql>CREATE OR REPLACE VIEW `v_items` AS | ||
+ | SELECT | ||
+ | i.ID, | ||
+ | i.CatNum, | ||
+ | i.isForSale, | ||
+ | i.isMaster, | ||
+ | i.qtyInStock, | ||
+ | i.isInPrint, | ||
+ | i.isCloseOut, | ||
+ | i.isPulled, | ||
+ | i.isDumped, | ||
+ | i.ID_Title, | ||
+ | i.ID_ItTyp, | ||
+ | i.ID_ItOpt, | ||
+ | i.ItOpt_Descr, | ||
+ | i.ID_ShipCost, | ||
+ | i.PriceBuy, | ||
+ | i.PriceSell, | ||
+ | i.PriceList, | ||
+ | i.Supp_CatNum, | ||
+ | io.Sort AS OptSort | ||
+ | FROM ( | ||
+ | `cat_items` AS `i` LEFT JOIN | ||
+ | `cat_ioptns` AS `io` ON | ||
+ | ((`i`.`ID_ItOpt` = `io`.`ID`)))</mysql> | ||
+ | Used by '''clsItemsExt''' | ||
+ | ====qryCat_Items==== | ||
<mysql>CREATE OR REPLACE VIEW qryCat_Items AS SELECT | <mysql>CREATE OR REPLACE VIEW qryCat_Items AS SELECT | ||
i.ID, | i.ID, | ||
Line 61: | Line 104: | ||
* '''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. | * '''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. | ||
− | + | ====qryCbx_Items==== | |
− | ===qryCbx_Items=== | ||
<mysql>CREATE OR REPLACE VIEW qryCbx_Items AS | <mysql>CREATE OR REPLACE VIEW qryCbx_Items AS | ||
SELECT ID, Descr | SELECT ID, Descr | ||
Line 215: | Line 257: | ||
GROUP BY ri.ID_Restock, ri.ID_Item, i.CatNum, i.Supp_CatNum, ri.CostExpPer, i.PriceSell, i.QtyMin_Stk | GROUP BY ri.ID_Restock, ri.ID_Item, i.CatNum, i.Supp_CatNum, ri.CostExpPer, i.PriceSell, i.QtyMin_Stk | ||
ORDER BY i.CatNum;</mysql> | ORDER BY i.CatNum;</mysql> | ||
+ | ===Stock=== | ||
+ | ====v_stk_lines_remaining==== | ||
+ | View listing stk_items actually in stock, which is actually different things: | ||
+ | * "forSale": items which are visible as "in stock" to customers | ||
+ | * "forShip": items which are available for shipping | ||
+ | * items neither for shipping nor for sale (probably some kind of recordkeeping purpose) | ||
+ | <mysql>CREATE OR REPLACE VIEW v_stk_lines_remaining AS | ||
+ | SELECT | ||
+ | st.ID, | ||
+ | st.ID_Bin, | ||
+ | st.ID_Item, | ||
+ | IF(sb.isForSale,st.Qty,0) AS QtyForSale, | ||
+ | IF(sb.isForShip,st.Qty,0) AS QtyForShip, | ||
+ | st.Qty AS QtyExisting, | ||
+ | st.CatNum, | ||
+ | st.WhenAdded, | ||
+ | st.WhenChanged, | ||
+ | st.WhenCounted, | ||
+ | st.Notes, | ||
+ | sb.ID_Place, | ||
+ | sp.Name AS WhName | ||
+ | FROM | ||
+ | ( | ||
+ | stk_items AS st | ||
+ | LEFT JOIN stk_bins AS sb | ||
+ | ON sb.ID=st.ID_Bin | ||
+ | ) | ||
+ | LEFT JOIN stk_places AS sp | ||
+ | ON sb.ID_Place=sp.ID | ||
+ | WHERE (st.WhenRemoved IS NULL) AND (sb.WhenVoided IS NULL) AND (st.Qty <> 0);</mysql> | ||
+ | ====v_stk_items_remaining==== | ||
+ | <mysql>CREATE OR REPLACE VIEW v_stk_items_remaining AS | ||
+ | SELECT | ||
+ | ID_Item, | ||
+ | SUM(QtyForSale) AS QtyForSale, | ||
+ | SUM(QtyForShip) AS QtyForShip, | ||
+ | SUM(QtyExisting) AS QtyExisting | ||
+ | FROM v_stk_lines_remaining | ||
+ | GROUP BY ID_Item;</mysql> | ||
+ | ====v_stk_byItemAndBin==== | ||
+ | <mysql>CREATE OR REPLACE VIEW v_stk_byItemAndBin AS | ||
+ | SELECT | ||
+ | ID_Item, | ||
+ | ID_Bin, | ||
+ | SUM(QtyForSale) AS QtyForSale, | ||
+ | SUM(QtyForShip) AS QtyForShip, | ||
+ | SUM(QtyExisting) AS QtyExisting | ||
+ | FROM v_stk_lines_remaining | ||
+ | GROUP BY ID_Item, ID_Bin | ||
+ | HAVING SUM(QtyExisting)>0;</mysql> | ||
+ | ====v_stk_byItemAndBin_wItemInfo==== | ||
+ | <mysql>CREATE OR REPLACE VIEW v_stk_byItemAndBin_wItemInfo AS | ||
+ | SELECT * FROM v_stk_byItemAndBin AS s LEFT JOIN qryCat_Items AS i ON s.ID_Item=i.ID;</mysql> | ||
+ | |||
+ | ====v_stk_byItemAndBin_wInfo==== | ||
+ | <mysql>CREATE OR REPLACE VIEW v_stk_byItemAndBin_wInfo AS | ||
+ | SELECT | ||
+ | s.*, | ||
+ | i.*, | ||
+ | b.ID_Place, | ||
+ | b.Code AS BinCode, | ||
+ | b.Descr AS BinDescr | ||
+ | FROM | ||
+ | ( | ||
+ | v_stk_byItemAndBin AS s LEFT JOIN qryCat_Items AS i ON s.ID_Item=i.ID | ||
+ | ) LEFT JOIN stk_bins AS b ON s.ID_Bin=b.ID;</mysql> | ||
+ | ===Caching=== | ||
+ | Caching should only be used for catalog display. | ||
+ | ====v_data_flow==== | ||
+ | <mysql> CREATE OR REPLACE VIEW v_data_flow AS | ||
+ | SELECT | ||
+ | df.ID_Srce, | ||
+ | df.ID_Dest, | ||
+ | df.ID_Proc, | ||
+ | dfx.doesClear | ||
+ | FROM data_flow AS df LEFT JOIN data_procs AS dfx ON df.ID_Proc=dfx.ID | ||
+ | ORDER BY dfx.doesClear;</mysql> |
Revision as of 12:20, 5 November 2007
Overview
"Views" in MySQL are like SELECT "queries" in MS Access, i.e. they pull data from existing tables and are themselves usable as data sources in much the same way that tables are (and in which result sets from functions are not).
Views
"Views" in MySQL are essentially identical to stored queries in MS Access.
Some common prefixes:
- qryCbx_: queries used for filling comboboxes. The unique ID will always be the first field, and the text to display will always be the second field; additional fields may be provided for use in further qryCbx_ queries which show a subset of the results.
- qryCat_: queries which build catalog numbers or information by joining multiple tables. (In Access, the convention was qryCatNum_.)
qryCat_Depts
<mysql>CREATE OR REPLACE VIEW qryCat_Depts AS
SELECT d.ID, d.Name, d.Sort, d.CatKey, d.ID_Supplier, CONCAT_WS('-',s.CatKey,d.CatKey) AS CatNum FROM depts AS d LEFT JOIN suppliers AS s ON d.ID_Supplier=s.ID;</mysql>
Titles
These will later reference cat_titles instead of titles, when that table is fully migrated.
v_titles
<mysql>CREATE OR REPLACE VIEW v_titles AS
SELECT t.*, tx.ID_Supp, tx.CatNum, tx.CatWeb, tx.cntForSale, tx.cntInPrint, tx.qtyInStock FROM titles AS t LEFT JOIN _titles AS tx on t.ID=tx.ID;</mysql>
qryCat_Titles
<mysql>CREATE OR REPLACE VIEW qryCat_Titles AS
SELECT t.ID, t.Name, CONCAT_WS('-',d.CatNum,t.CatKey) AS CatNum, d.ID_Supplier, t.ID_Dept, t.DateAdded, t.RstkMin AS QtyMin_Rstk, t.Notes, t.Supplier_CatNum AS Supp_CatNum FROM titles AS t LEFT JOIN qryCat_Depts AS d ON t.ID_Dept=d.ID;</mysql>
Items
v_items
<mysql>CREATE OR REPLACE VIEW `v_items` AS
SELECT i.ID, i.CatNum, i.isForSale, i.isMaster, i.qtyInStock, i.isInPrint, i.isCloseOut, i.isPulled, i.isDumped, i.ID_Title, i.ID_ItTyp, i.ID_ItOpt, i.ItOpt_Descr, i.ID_ShipCost, i.PriceBuy, i.PriceSell, i.PriceList, i.Supp_CatNum, io.Sort AS OptSort FROM ( `cat_items` AS `i` LEFT JOIN `cat_ioptns` AS `io` ON ((`i`.`ID_ItOpt` = `io`.`ID`)))</mysql>
Used by clsItemsExt
qryCat_Items
<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.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>
- 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.
qryCbx_Items
<mysql>CREATE OR REPLACE VIEW qryCbx_Items AS SELECT ID, Descr FROM qryCbx_Items_data;
CREATE OR REPLACE VIEW qryCbx_Items_active AS SELECT ID, Descr FROM qryCbx_Items_data WHERE NOT isPulled;
CREATE OR REPLACE VIEW qryCbx_Items_data AS SELECT
i.ID, CAST( CONCAT( if(i.isPulled, CONCAT('!X '), ), if(i.CatNum IS NULL, CONCAT('#',i.ID,' (no cat#)') ,i.CatNum), if(i.Supp_CatNum IS NULL, , CONCAT(' [',i.Supp_CatNum,']') ), if(i.isForSale,,' *n/a*'), ' ', if(i.Descr IS NULL,'no description',i.Descr) ) AS CHAR ) AS Descr, i.isPulled, i.isForSale, i.ID_Title, i.CatNum, i.Supp_CatNum, i.PriceSell, i.QtyMin_Stk
FROM qryCat_Items AS i ORDER BY NOT i.isPulled, i.isForSale, i.CatNum;</mysql>
- Future refinement: no need to display CatNum, Supp_CatNum, i.Descr if i.isPulled
- Added more fields for forms which want to display Descr from this query but also need those fields (e.g. Restock Items form):
- ID_Title, CatNum, PriceSell, QtyMin_Stk
- Added isPulled so qryCbx_Items_active can display only non-pulled items (less bandwidth = faster loading)
qryCbx_Orders
<mysql>CREATE OR REPLACE VIEW qryCbx_Orders AS SELECT
ID, CONCAT_WS(' due ',CAST(Number AS CHAR),DATE_FORMAT(WhenNeeded,'%Y-%m-%d')) AS Descr
FROM core_orders ORDER BY CONCAT(IFNULL(SortPfx,),Number) DESC;</mysql>
qryCbx_Restocks
I don't know WTF is going on here. MySQL wouldn't let me create this as one big query (no error message, just "query cancelled"), so I tried to split it into two parts and union them -- but I get the same error message. For now, I'm just defining the subqueries in MySQL and the UNION in Access. :-P <mysql>CREATE OR REPLACE VIEW qryCbx_Restocks_byStatus AS SELECT
r.ID, CAST( CONCAT( CAST(COALESCE( CONCAT('X ',DATE_FORMAT(r.WhenKilled,'%Y-%m-%d')), CONCAT('R ',DATE_FORMAT(r.WhenReceived,'%Y-%m-%d')), CONCAT('O ',DATE_FORMAT(r.WhenOrdered,'%Y-%m-%d')), CONCAT('D ',DATE_FORMAT(r.WhenDebited,'%Y-%m-%d')), CONCAT('C ',DATE_FORMAT(r.WhenCreated,'%Y-%m-%d')) ) AS BINARY), ' ', s.CatKey, IFNULL(CONCAT(' [',r.PurchOrdNum,']'),) ) AS CHAR ) AS Descr, NOT IsLocked AS IsOpen, r.WhenOrdered, r.WhenReceived, r.WhenConfirmed, r.ID_Supplier
FROM core_restocks AS r LEFT JOIN cat_supp AS s ON r.ID_Supplier=s.ID ORDER BY COALESCE(r.WhenKilled,r.WhenReceived,r.WhenOrdered,r.WhenDebited,r.WhenCreated);
CREATE OR REPLACE VIEW qryCbx_Restocks_byPurchOrd AS SELECT
r.ID, CAST(r.PurchOrdNum AS CHAR) AS Descr, NOT IsLocked AS IsOpen, r.WhenOrdered, r.WhenReceived, r.WhenConfirmed, r.ID_Supplier
FROM core_restocks AS r WHERE r.PurchOrdNum IS NOT NULL ORDER BY r.PurchOrdNum;
CREATE OR REPLACE VIEW qryCbx_Restocks AS
SELECT * FROM qryCbx_Restocks_byStatus
UNION
SELECT * FROM qryCbx_Restocks_byPurchOrd;</mysql>
qryFrm_RestockLines
<mysql>CREATE OR REPLACE VIEW qryFrm_RestockLines AS SELECT
ri.ID, ri.ID_Restock, ri.ID_Item, ci.CatNum, ci.Supp_CatNum, ci.PriceSell, ci.QtyMin_Stk, o.Number AS OrderName, ci.Descr AS ItemDescr, o.WhenNeeded, If(t.RstkMin = 1, , t.RstkMin) AS RstkMinStr, ri.QtyOrd, ri.QtyNeed, ri.QtyExp, ri.InvcLineNo, ri.InvcQtyOrd, ri.InvcQtySent, ri.isGone, ri.QtyRecd, ri.QtyFiled, ri.CostExpPer, ri.CostInvPer, ri.CostInvTot, ri.CostActTot, ri.CostActBal, ri.Notes
FROM (
(rstk_lines AS ri LEFT JOIN core_orders AS o ON ri.ID_Order=o.ID) LEFT JOIN qryCbx_Items_data AS ci ON ri.ID_Item=ci.ID)
LEFT JOIN cat_titles AS t ON ci.ID_Title=t.ID;</mysql>
- A lot of these fields were added back when this query was still the data source for qryFrm_RestockLines_byItem; this is no longer the case. Probably the only customer for this query is the Restock Items entry form, so any fields not needed there can probably be done away with.
qryFrm_RestockLines_byItem
Basically the same as qryFrm_RestockLines, but grouped by ID_Item and with more Item-related fields <mysql>CREATE OR REPLACE VIEW qryFrm_RestockLines_byItem AS SELECT
CONCAT(ri.ID_Item,'-',ri.ID_Restock) AS Key_RstkItem, ri.ID_Item, ri.ID_Restock, i.Descr AS ItemDescr, i.CatNum, i.Supp_CatNum, SUM(IFNULL(ri.QtyOrd,0)) AS QtyOrd, SUM(IFNULL(ri.QtyExp,0)) AS QtyExp, ri.CostExpPer, i.PriceSell, i.QtyMin_Stk
FROM rstk_lines AS ri LEFT JOIN qryCbx_Items_data AS i ON ri.ID_Item=i.ID GROUP BY ri.ID_Restock, ri.ID_Item, i.CatNum, i.Supp_CatNum, ri.CostExpPer, i.PriceSell, i.QtyMin_Stk ORDER BY i.CatNum;</mysql>
Stock
v_stk_lines_remaining
View listing stk_items actually in stock, which is actually different things:
- "forSale": items which are visible as "in stock" to customers
- "forShip": items which are available for shipping
- items neither for shipping nor for sale (probably some kind of recordkeeping purpose)
<mysql>CREATE OR REPLACE VIEW v_stk_lines_remaining AS
SELECT st.ID, st.ID_Bin, st.ID_Item, IF(sb.isForSale,st.Qty,0) AS QtyForSale, IF(sb.isForShip,st.Qty,0) AS QtyForShip, st.Qty AS QtyExisting, st.CatNum, st.WhenAdded, st.WhenChanged, st.WhenCounted, st.Notes, sb.ID_Place, sp.Name AS WhName FROM ( stk_items AS st LEFT JOIN stk_bins AS sb ON sb.ID=st.ID_Bin ) LEFT JOIN stk_places AS sp ON sb.ID_Place=sp.ID WHERE (st.WhenRemoved IS NULL) AND (sb.WhenVoided IS NULL) AND (st.Qty <> 0);</mysql>
v_stk_items_remaining
<mysql>CREATE OR REPLACE VIEW v_stk_items_remaining AS
SELECT ID_Item, SUM(QtyForSale) AS QtyForSale, SUM(QtyForShip) AS QtyForShip, SUM(QtyExisting) AS QtyExisting FROM v_stk_lines_remaining GROUP BY ID_Item;</mysql>
v_stk_byItemAndBin
<mysql>CREATE OR REPLACE VIEW v_stk_byItemAndBin AS
SELECT ID_Item, ID_Bin, SUM(QtyForSale) AS QtyForSale, SUM(QtyForShip) AS QtyForShip, SUM(QtyExisting) AS QtyExisting FROM v_stk_lines_remaining GROUP BY ID_Item, ID_Bin HAVING SUM(QtyExisting)>0;</mysql>
v_stk_byItemAndBin_wItemInfo
<mysql>CREATE OR REPLACE VIEW v_stk_byItemAndBin_wItemInfo AS
SELECT * FROM v_stk_byItemAndBin AS s LEFT JOIN qryCat_Items AS i ON s.ID_Item=i.ID;</mysql>
v_stk_byItemAndBin_wInfo
<mysql>CREATE OR REPLACE VIEW v_stk_byItemAndBin_wInfo AS
SELECT s.*, i.*, b.ID_Place, b.Code AS BinCode, b.Descr AS BinDescr FROM ( v_stk_byItemAndBin AS s LEFT JOIN qryCat_Items AS i ON s.ID_Item=i.ID ) LEFT JOIN stk_bins AS b ON s.ID_Bin=b.ID;</mysql>
Caching
Caching should only be used for catalog display.
v_data_flow
<mysql> CREATE OR REPLACE VIEW v_data_flow AS
SELECT df.ID_Srce, df.ID_Dest, df.ID_Proc, dfx.doesClear FROM data_flow AS df LEFT JOIN data_procs AS dfx ON df.ID_Proc=dfx.ID ORDER BY dfx.doesClear;</mysql>