SQL (Structured Query Language) Best Practices
SQL Format
-
Capitalize all letters in SQL keywords such as
SELECT,JOIN
orWHERE
. -
Each string (text) variable in a SQL statement should be enclosed in single quotes. A list of more than one variable must be enclosed in parentheses. Example: ('Alice', 'Bruce', 'Cathy', 'Doug')
-
A list of integer variables in a SQL statement must be enclosed in parentheses. Single quotes must not be used. Example: (1,2,3,4).
-
Avoid using SQL inside Math Loops.
-
SQLs needed in MathLoops should be handled outside the loop if at all possible.
-
SQLs may fill arrays and then the arrays should be indexed inside the loop for manipulation.
-
SQL queries should return a single value and a single row for most math variable types and field data types.
-
Use SQL functions to ensure one value is returned when appropriate. Functions such as SUM, MIN, MAX, etc., ensure the return of one value.
- Exceptions to returning a single value and single row are:
Combo boxes, 2 values, multiple rows
Radio buttons, 2 values, multiple rows
Collections, 2 values, multiple rows
StoredProcedure-Collection, 2 values, multiple rows
Arrays, 1 value, multiple rows
-
COLLECTION SQL should return two elements: Name and Value with multiple rows.
-
In a
COLLECTION
, if theKEY
is the same name as a field name, then concatenate a string to the first column returned in the COLLECTION SQL. -
If possible, avoid using a name for the math variable that could be the same as a potential
COLLECTION KEY
value. -
SQL that is used to fill a combo box for a field should return two values and multiple rows.
-
One value is the Option Value, and the other value is the Text Value.
-
If possible, subqueries should be avoided and JOINs should be used for better performance.
-
Organize table names used in SQLs with the selection criteria of that table. The criteria used in the WHERE clause should match to the table associated with the FROM clause. Criteria from other tables (through JOINs) should use AND within their JOIN clauses.
-
Only one table name should be after
FROM
. The remaining tables will be part of the SQL throughJOIN.
Retrieving Data
-
Use SQL to retrieve data from the database.
-
Do not use SQL to manipulate dates.
-
Avoid the SQL
CASE
statement where possible. -
Don’t
JOIN
tables unnecessarily. -
For example, AsActivity contains PolicyGUID, therefore there is no need to
JOIN
AsPolicy to retrieve PolicyGUID. -
When performing SQL to select one or more AsActivity records, be sure to consider AsActivity.StatusCode and AsActivity.TypeCode values to include/exclude records as desired.
-
Avoid returning pending, shadow, undo, redo, etc., records that are not needed.
-
For AsActivity.StatusCode,
-
For active activity status, use 01 and 14 together.
-
For pending activity status, use 02, 09 and 13 together.
-
When querying for the most recent transaction of a certain type, query by EffectiveDate DESC, ProcessingOrder DESC, ActivityGMT DESC, and then fetch the first row only.
-
Use
PLANFIELD, POLICYFIELD,
orSEGMENTFIELD
when retrieving information from AsPlanField, policy information, or a segment. -
For
SEGMENTFIELD
retrieval, if more than one field is to be returned, and not in a segment loop, then query theSegmentGUID
and insert into an array. -
Initialize a MathVariable to hold the array's index value.
-
With these two variables, you can now use
SEGMENTFIELD
.
Example XML
<MathVariable VARIABLENAME="SegmentGUIDArray" TYPE="NUMERICARRAY" OPERATION="FILLBY-SQL">Write SQL to retrieve the SegmentGUID that you would like to retrieve a Segment Field</MathVariable>
<MathVariable VARIABLENAME="SegmentGUIDArray_index" TYPE="VALUE">0</MathVariable>
<MathVariable VARIABLENAME="SegmentFieldRetrieved" TYPE="SEGMENTFIELD" SOURCEARRAY="SegmentGUIDArray">Name of the Field you would like to retrieve</MathVariable>
-
In fields, when defining a combo box, use SQL queries calling ASCODE rather than redefining as fixed queries in business rules when possible. This will help with standardization and maintenance.
-
Information may need to be added to AsCode and must be cleared with a Configuration Lead.
-
Use SQL as little as possible. Use
COLLECTION
to bring back as much usable data as possible in one round trip to the database. Use CollectionValue to parse the individual pieces of data from theCOLLECTION