Your browser does not support JavaScript!
You are here: Configuration Overview > Configuration Standards > SQL (Structured Query Language) Best Practices

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.

  5. SQL queries should return a single value and a single row for most math variable types and field data types.  

  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.

  8. SQL that is used to fill a combo box for a field should return two values and multiple rows.  

  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.

  2. Don’t JOIN tables unnecessarily.  

  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.

  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.

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. 

  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

Copyright © 2009, 2015, Oracle and/or its affiliates. All rights reserved. Legal Notices