SQL compatibility rules

The following pitfalls exist when trying to support several databases. Please stick to these rules!

• test for a null value: use IS NULL.
  • (e.g. ... OR p_date_good_until_d IS NULL)
• ORDER BY for queries that are joined with UNION cannot work with aliases for the columns, you have to use the number for the column
  • (e.g. ... ORDER BY 1)
• boolean variables need to be compared with true or false
  • (e.g. WHERE p_deleted_partner_l = false AND ...)
• don't use JOIN. Please explicitly write the join inside the WHERE clause.
  • see the remarks in:
    • pro business: SQL Style Guide
    • Tina London's Guidelines and good practice Guide for developing SQL

Please note that we reference all Table names with PUB_ before the name, eg. PUB_a_account. This is necessary, so that we can check for permissions and other things. For an example see this snippet from DataAggregates.PPartnerAddress.cs:

1952             OtherLocations = Convert.ToInt16(DBAccess.GDBAccessObj.ExecuteScalar(
1953                "SELECT COUNT(*) " + "FROM PUB_" + PPartnerLocationTable.GetTableDBName() + ' ' +
1954                "WHERE " + PPartnerLocationTable.GetPartnerKeyDBName() + " = ? " +
1955		     "AND " + PPartnerLocationTable.GetLocationKeyDBName() + " NOT IN " +
1956	             "(" + LocationKeyInString + ")", AReadTransaction, false, ParametersArray));

The Report Generator is able to convert the following pitfalls. You only need to know about them, when you are debugging.

• Strings are written in single quotes.
  • (e.g. WHERE p_status_code_c = 'ACTIVE')
• Dates need to be written in Amercian format, i.e. 'mm/dd/yyyy'.
  • Using the notation {#date_variable#) works from the reports, if you don't need a static date
• boolean comparisons: "true" needs to be written as "1", "false" is "0".
• The tables have the prefix "pub." instead of "pub_"

To get the days of year of a specific date in the sql statement use DAYOFYEAR(). This will return a string from 000 to 366. DAYOFYEAR() will be converted to the correct function for the different databases. For example DAYOFYEAR(person.p_date_of_birth_d) will be converted to:

• SqLite: strftime( %j, person.p_date_of_birth_d)
• PostgreSql: to_char(person.p_date_of_birth_d, 'DDD')
• MySql: DATE_FORMAT(person.p_date_of_birth_d, %j)