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. If possible, subqueries should be avoided and JOINs should be used for better performance.

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

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

  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

 

 

 

 

Oracle Insurance Logo Copyright © 2017, Oracle and/or its affiliates. All rights reserved. About Oracle Insurance | Contact Us