FinanceFerret/2.0/sql/qryCbx Accts: Difference between revisions

Revision as of 22:56, 30 August 2009

Purpose: dataset for drop-down boxes
Fields:
- ID: The way this is generated is a bit of a kluge so that each line's "value" will be unique, even though some lines may have "aliases". The HTML drop-down list control seems to reject duplicate values. Appending the ID of the "alias" to the ID we actually want used gives a unique value per line which can easily be converted to the proper value by taking only the integer part.
History:
- 2009-08-30 Created for web interface version

<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCbx_Accts AS SELECT

 CONCAT_WS('.',ID_Alias,ID) AS ID,
 Name

FROM Accounts ORDER BY Name;</mysql> <section end=sql />

@@ Line 1: / Line 1: @@
 ==About==
 * '''Purpose''': dataset for drop-down boxes
+* '''Fields''':
+** '''ID''': The way this is generated is a bit of a kluge so that each line's "value" will be unique, even though some lines may have "aliases". The HTML drop-down list control seems to reject duplicate values. Appending the ID of the "alias" to the ID we actually want used gives a unique value per line which can easily be converted to the proper value by taking only the integer part.
 * '''History''':
 ** '''2009-08-30''' Created for web interface version
@@ Line 6: / Line 8: @@
 <section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCbx_Accts AS
 SELECT
-   IFNULL(a.ID_Alias,a.ID) AS ID,
+   CONCAT_WS('.',ID_Alias,ID) AS ID,
-   CONCAT_WS(' < ',a.Name,ap.Name) AS Name
+   Name
-FROM Accounts AS a
+FROM Accounts
-  LEFT JOIN Accounts AS ap
-  ON a.ID_Parent=ap.ID
 ORDER BY Name;</mysql>
 <section end=sql />