SQL (Structured Query Language) Best Practices

SQL Format

  1. Capitalize all letters in SQL keywords such as SELECT,JOIN or WHERE.

  2. 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')

  3. 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).

  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. 

  5. 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

  6. COLLECTION SQL should return two elements: Name and Value with multiple rows.

  7. In a COLLECTION, if the KEY 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 KEYvalue. 

  8. 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.

  9. The system with a DB2 database cannot determine the numeric scale of the result of some built-in functions. Functions such as SUM(), MIN() and MAX() fall into this category. The handoff between database server and OIPA performs an automatic round on the result which is not preferred. A statement in Oracle or SqlServer such as the following could return a result of 1.50.

    SELECT CAST(COALESCE(SUM(AsActivityField.FloatValue),0.00) AS FLOAT) FROM …

    In DB2 the syntax above is acceptable, but will result in 2 with the same intermediate result. The safe DB2 statement that eliminates the rounding and equates to the above is:

    SELECT CAST(COALESCE(SUM(AsActivityField.FloatValue) , 0.00) AS DECIMAL(31,10)) FROM …

    With this statement, the DB2 driver can see the explicitly defined scale of the result. This syntax is acceptable in all three database platforms. In DB2, the scale is known where the result comes directly from a float column without aggregation through a function so return of float values do not need to be universally modified to casts of numeric data types that indicate scale.

  10. If possible, subqueries should be avoided and JOINs should be used for better performance.

  11. 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. 

  12. Only one table name should be after FROM. The remaining tables will be part of the SQL through  JOIN.

Retrieving Data

  1. Use SQL to retrieve data from the database.

    • Do not use SQL to manipulate dates.

    • Avoid the SQL CASE statement where possible.

  2. Don’t JOIN tables unnecessarily.  

    • For example, AsActivity contains PolicyGUID, therefore there is no need to JOIN AsPolicy to retrieve PolicyGUID. 

  3. 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. 

  4. 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.

  5. Use PLANFIELD, POLICYFIELD, or SEGMENTFIELD 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 the SegmentGUID 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="STRINGARRAY" 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>   

 

  1. 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.

  2. 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 the COLLECTION