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

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)