Dialect 3 Troubleshooting

Possible Errors in Reports when switching to Firebird Dialect 3 in Communicare V18.3 and later.

In Communicare V18.3, the database dialect changed from dialect 1 to dialect 3. This change introduced a stricter standard of SQL and new reserved words, meaning some custom reports in Communicare may require modification.

Below is a list of possible errors caused by the dialect change, and how to fix them. Should the error persist, please contact Communicare Support for further assistance.

Table 1. Dialect 3 errors
Error Possible Cause Solution
Access violation Double quotes “ are not valid string delimiters in Dialect 3. In some cases, a double quote will cause an Access Violation, in other cases, a more obvious SQL error. Replace double quotes (“) with single quotes (‘).

Dynamic SQL Error: SQL error code = -206. Column unknown.

 Column does not belong to referenced table. Example 1:
select p.pat_id
from pat_measure me
join pat_morb_view xpm on me.morb_no = xpm.morb_no
join patient p on p.pat_id = xpm.pat_id
join (morbidity_measurement mm
join morb_type_view mt on mt.morb_type_no = mm.morb_type_no
and p.date_of_birth = 'today'
) on me.measure_type_no = mm.measure_type_no
 
select p.pat_id
from pat_measure me
join pat_morb_view xpm on me.morb_no = xpm.morb_no
join patient p on p.pat_id = xpm.pat_id
join (morbidity_measurement mm
join morb_type_view mt on mt.morb_type_no = mm.morb_type_no
) on me.measure_type_no = mm.measure_type_no
and p.date_of_birth = 'today' --moved to here
Example 2
Select …
from pat_morb_view pm
left outer join (pat_encounter_view pe
join encounter_mode_place emp on
emp.enc_place_no = pm.enc_place_no
and emp.enc_mode_no = pe.enc_mode_no
join encounter_mode em on em.enc_mode_no = emp.enc_mode_no)
on pe.enc_no = pm.enc_no
join patient p on p.pat_id = pm.pat_id
 
Select …
from pat_morb_view pm
left outer join (pat_encounter_view pe
join encounter_mode_place emp on emp.enc_place_no = pe.enc_place_no
and emp.enc_mode_no = pe.enc_mode_no
join encounter_mode em on em.enc_mode_no = emp.enc_mode_no)
on pe.enc_no = pm.enc_no
join patient p on p.pat_id = pm.pat_id

Dynamic SQL Error: Expression evaluation not supported.

 Strings cannot be added or subtracted in dialect 3. For example:
‘TODAY’ – 366
Also (frequently used in reports):
(START_DATE<(:Last_date_to_report + 1))
 Cast the dates first:
(CAST('TODAY' AS DATE) – 366
(START_DATE<(cast(:Last_date_to_report as DATE) + 1))

Datatypes are not comparable in expression UNION.

 A union has different datatypes in the select statement

Need to make sure that the columns in the union are the same datatype.

We now use TIMESTAMPs instead of DATE fields.

Dynamic SQL Error: Expression evaluation not supported.

 Invalid data type in DATE/TIME/TIMESTAMP addition or subtraction in add_datetime(). Example 1:
current_date - pmv.pat_morb_act_date project_date
 
datediff(day from pmv.pat_morb_act_date to current_date) project_date
Example 2:
modified_date > :param + 1
where :param can have values like ‘today’
 
modified_date - 1 > :param

Dynamic SQL Error: Expression evaluation not supported

 Strings cannot be multiplied in dialect 3. For example:
evr.sys_code * we.pat_measure_value priority
where sys_code is a CHAR(3) and pat_measure_value is numeric
 Cast the string as a Numeric
CAST(evr.sys_code AS NUMERIC) * we.pat_measure_value priority

Arithmetic overflow or division by zero has occurred.
Arithmetic exception, numeric overflow, or string truncation. For example: 
cast(cast( pm.pat_morb_act_date as char(11))as date)
 
cast( pm.pat_morb_act_date as date)

A number of new reserved keywords are introduced.

 Ensure your DSQL statements and procedure/trigger sources don't contain those keywords as identifiers. Otherwise, you'll need to either use them quoted (in Dialect 3 only) or rename them, or add an underscore, which the reports will ignore, thus avoiding changes to the report layouts. For example:
count(case when pat_sex is null then pat_id else null end) unknown
 
count(case when pat_sex is null then pat_id else null end) _unknown
Mixed explicit and implicit joins Improperly mixed explicit and implicit joins are not supported anymore, as per the SQL specification. It also means that in the explicit A JOIN B ON <condition>, the condition is not allowed to reference any stream except A and B. See examples above.
FieldName: ______ not found This could be a missing alias for a cast in the parameters section
<SHOW_SENT_CLAIMS
STATEMENT="select cast('Yes' as VarChar(3))
from rdb$database
union
select cast('No' as VarChar(3))
from rdb$database"
DEFAULT="No"
>
 
<SHOW_SENT_CLAIMS
STATEMENT="select cast('Yes' as VarChar(3)) display_field
from rdb$database
union
select cast('No' as VarChar(3)) display_field
from rdb$database"
DEFAULT="No"
>

No error but incorrect data whenever dividing two integers where the result is an integer.

 In dialect 1 the result of dividing two integers is rounded up or down (14/10 = 1 and 15/10 = 2) but in dialect 3 it is always rounded down (14/10 and 15/10 are both 1) When dividing integers always cast integers as floats before doing the division.
String Overflow Casting a timestamp that has a time as varchar(11) now results in a string overflow issue. This technique was used in the distant past before we had the cu_formatdatetime function. The offending code looks like this:
cast(pe.start_date as varchar(11))
 Use the UDF:
cu_formatdatetime(pe.start_date, 'DD-MMM-YYYY')
Other tips:
  • All DATE fields that need to contain a time need to be changed to a TIMESTAMP
  • All DATE fields that need to contain a time ONLY need to be changed to a TIME